mysql 统计每列即每个字段下的0,1,-1的个数

问题已经解决
图片说明
数据如图,我想求这个表中每个字段下0,1,-1的个数,即 air-control-type下有多少个0,多少个1,多少个-1;air-independent-back 下 0,1,-1的个数。。。。

注意效率,一条语句跑完最好

6个回答

SELECT COUNT(CASE WHEN (A.air-control-type=0) THEN 0 END),
COUNT(CASE WHEN (A.air-control-type=1) THEN 0 END),
COUNT(CASE WHEN (A.air-control-type=-1) THEN 0 END),
COUNT(CASE WHEN (A.air-independent-back=0) THEN 0 END),
COUNT(CASE WHEN (A.air-independent-back=1) THEN 0 END),
COUNT(CASE WHEN (A.air-independent-back=-1) THEN 0 END)
FROM T_TABLE AS S

ii950606
踮脚敲代码 回复azuoyayun: 如果某一项没有0,数据就不出来了
一年多之前 回复
azuoyayun
azuoyayun select air-control-type,count(air-control-type) num from count1 group by air-control-type; select air-independent-back,count(air-independent-back) num from count1 group by air-independent-back;
一年多之前 回复
ii950606
踮脚敲代码 SELECT COUNT(CASE WHEN (A.air-control-type=0) THEN 0 END), COUNT(CASE WHEN (A.air-control-type=1) THEN 0 END), COUNT(CASE WHEN (A.air-control-type=-1) THEN 0 END), COUNT(CASE WHEN (A.air-independent-back=0) THEN 0 END), COUNT(CASE WHEN (A.air-independent-back=1) THEN 0 END), COUNT(CASE WHEN (A.air-independent-back=-1) THEN 0 END) FROM T_TABLE AS A
一年多之前 回复

select
field_name,
field_value,
field_value_count
from
(
select 'c1' as field_name , c1 as field_value, count(1) field_value_count as cnt from my_table group by c1
union all
select 'c2' as field_name , c2 as field_value, count(1) field_value_count as cnt from my_table group by c2
union all
select 'c3' as field_name , c3 as field_value, count(1) field_value_count as cnt from my_table group by c3
) tmp

看看上面行不?行的话,还望赏点分

SELECT SUM( DECODE(air-control-type,0,1,0)) air-control-type0,
SUM( DECODE(air-control-type,1,1,0)) air-control-type1,
SUM( DECODE(air-control-type,-1,1,0)) air-control-type-1 ,.....FROM TABLE

jdz199409
姜某人 回复qq_29127157: 已经实现这个功能,采用方法与此相同,可以的话请重新编辑答案,好给您悬赏分
一年多之前 回复
qq_29127157
qq_29127157 回复姜某人: SELECT SUM(case when air-control-type = 1 then 1 else 0 end) air-control-type1, SUM(case when air-control-type = 0 then 1 else 0 end) air-control-type0, SUM(case when air-control-type = 0 then 1 else 0 end) air-control-type-1,... FROM TABLENAME
一年多之前 回复
jdz199409
姜某人 貌似decode不是这么用的吧
一年多之前 回复
SELECT air-control-type,COUNT(air-control-type),air-independent-back,COUNT(air-independent-back) FROM tablename GROUP BY air-control-type,air-independent-back
jdz199409
姜某人 回复恒陀: 实现思路大致相同
一年多之前 回复
jdz199409
姜某人 回复恒陀: 感谢,我在一个dba 的帮助下完成了这个功能,实现方式与您相同
一年多之前 回复
azuoyayun
azuoyayun 这个是不行的,多个分组,获取到的数据不对
一年多之前 回复
yangheng869
杨无邪 回复姜某人: insert into TABLE (air-control-type,air-independent-back,...) values (-1,-1,-1),(0,0,0),(1,1,1); select * from (select air-control-type,count(*)-1 count_air-control-type from TABLE group by air-control-type) c1 join (select air-independent-back,count(*)-1 count_air-independent-back from TABLE group by air-independent-back)c2 on c1.air-control-type=c2.air-independent-back join ... 也可以直接使用join+union实现,这样可以不用insert了,不过字段有点多的话写起来比较麻烦
一年多之前 回复
jdz199409
姜某人 你这纯属胡闹
一年多之前 回复

select sum(case when air-control-type = 0 then 1 else 0 end) zero_count,
sum(case when air-control-type= 1 then 1 else 0 end) one_count from table;
你看看这个可不可以

select count(air-control-type = 0 or null) typeZeroCount, count(air-control-type = 1 or null) typeOneCount, count(air-control-type = -1 or null) typeNegativeCount from table;

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!