MySQL题目讨论

Pandolar

create table tb_count(
    id int not null auto_increment,
    col1 enum('yes', 'no'),
    col2 enum('yes', 'no'),
    col3 enum('yes', 'no'),
    col4 enum('yes', 'no'),
    primary key(id)
) default charset=utf8;

insert into tb_count values (null, 'yes', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'yes', 'no', 'no');
insert into tb_count values (null, 'no', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'no', 'no', 'no');

select * from tb_count;

-- 求：仅使用1条SQL语句求每列中yes/no出现的次数

# 要求输出的表如下：
# value col1  col2  col3  col4
# yes    1      3    0      2
# no     3      1    4      2

x_kotaku

这有点难度 占个座等高手

魔术蝎

select  'yes' as 'value',ROUND(((char_LENGTH(col1) - char_LENGTH( REPLACE (col1, 'yes', ''))))/3) as 'col1',ROUND(((char_LENGTH(col2) - char_LENGTH( REPLACE (col2, 'yes', ''))))/3) as 'col2',ROUND(((char_LENGTH(col3) - char_LENGTH( REPLACE (col3, 'yes', ''))))/3) as 'col3',ROUND(((char_LENGTH(col4) - char_LENGTH( REPLACE (col4, 'yes', ''))))/3) as 'col4'  from(
select GROUP_CONCAT(col1) col1, GROUP_CONCAT(col2) col2, GROUP_CONCAT(col3) col3, GROUP_CONCAT(col4) col4 from tb_count
) t
union all
select 'no' as 'value',ROUND( ((char_LENGTH(col1) - char_LENGTH( REPLACE (col1, 'no', ''))))/2) as 'col1',ROUND( ((char_LENGTH(col2) - char_LENGTH( REPLACE (col2, 'no', ''))))/2) as 'col2',ROUND(((char_LENGTH(col3) - char_LENGTH( REPLACE (col3, 'no', ''))))/2) as 'col3', ROUND(((char_LENGTH(col4) - char_LENGTH( REPLACE (col4, 'no', ''))))/2) as 'col4'  from(
select GROUP_CONCAT(col1) col1, GROUP_CONCAT(col2) col2, GROUP_CONCAT(col3) col3, GROUP_CONCAT(col4) col4 from tb_count
) t


--------------------------------------
长是长了点，但是还是实现了。效率问题就没考虑了，期待大佬的回复

smldhz

select 'yes'as value,sum(col1='yes')as col1,sum(col2='yes')as col2,sum(col3='yes')as col3,sum(col4='yes')as col4 from tb_count union select 'no',sum(col1='no'),sum(col2='no'),sum(col3='no'),sum(col4='no') from tb_count;

倾何

[SQL] 纯文本查看 复制代码

SELECT
	'yes' AS 'value',
	SUM( CASE WHEN col1 = 'yes' THEN 1 ELSE 0 END ) AS col1,
	SUM( CASE WHEN col2 = 'yes' THEN 1 ELSE 0 END ) AS col2,
	SUM( CASE WHEN col3 = 'yes' THEN 1 ELSE 0 END ) AS col3,
	SUM( CASE WHEN col4 = 'yes' THEN 1 ELSE 0 END ) AS col4 
FROM
	tb_count
	UNION ALL
SELECT
	'no' AS 'value',
	SUM( CASE WHEN col1 = 'no' THEN 1 ELSE 0 END ) AS col1,
	SUM( CASE WHEN col2 = 'no' THEN 1 ELSE 0 END ) AS col2,
	SUM( CASE WHEN col3 = 'no' THEN 1 ELSE 0 END ) AS col3,
	SUM( CASE WHEN col4 = 'no' THEN 1 ELSE 0 END ) AS col4 
FROM
	tb_count

没想到不用`UNION `之类怎么做

xuanmuluck

这是个变相的二维表,数据库通常处理的是一维表吧

墨晨

SELECT 'yes' ,sum( if(col1='yes',1,0)) col1,sum( if(col2='yes',1,0)) col2,sum( if(col3='yes',1,0)) col3,sum( if(col4='yes',1,0)) col4 FROM tb_count
union all
SELECT 'no',sum( if(col1='no',1,0)) col1,sum( if(col2='no',1,0)) col2,sum( if(col3='no',1,0)) col3,sum( if(col4='no',1,0)) col4 FROM tb_count;

Pandolar

xuanmuluck 发表于 2021-12-6 08:44
这是个变相的二维表,数据库通常处理的是一维表吧

所以拿出来一起讨论一下

xuanmuluck

Pandolar 发表于 2021-12-6 10:08
所以拿出来一起讨论一下

其实我曾经也有过这样的想法,后来想想为什么excel很容易做到的事情,sql就很费事呢,想明白了以后就没弄过,原来也有和我同样想法的人

倾何

[SQL] 纯文本查看 复制代码

SELECT 
  IF(y.yn=1,'yes','no')
, SUM(y.yn = col1) AS col1
, SUM(y.yn = col2) AS col2
, SUM(y.yn = col3) AS col3
, SUM(y.yn = col4) AS col4
FROM `tb_count` AS t
JOIN (SELECT 1 AS yn UNION ALL SELECT 2)  AS y
GROUP BY y.yn;


//8.0
WITH t(value) AS
(
 SELECT 'yes' UNION ALL
 SELECT 'no'
)
SELECT value,
       SUM( col1 = value ) AS col1,
       SUM( col2 = value ) AS col2,
       SUM( col3 = value ) AS col3,
       SUM( col4 = value ) AS col4 
 FROM tb_count,
      t
GROUP BY value
ORDER BY value DESC

大佬给的