有一个table(A,B,C)内容如下:A B C
湖北 武汉 武昌区
湖南 长沙 长沙区
湖北 武汉 武昌区
湖南 岳阳 岳阳区
海南 海口 秀英区
湖北 武汉 汉阳区
湖北 武汉 汉口区
海南 海口 龙华区
海南 三亚 河东区
------------------------如何统计A中相同字段数量,其中B中相同字段数量,C中相同数量
湖北 武汉 武昌区
湖南 长沙 长沙区
湖北 武汉 武昌区
湖南 岳阳 岳阳区
海南 海口 秀英区
湖北 武汉 汉阳区
湖北 武汉 汉口区
海南 海口 龙华区
海南 三亚 河东区
------------------------如何统计A中相同字段数量,其中B中相同字段数量,C中相同数量
给你一个SQL做参考
select A,count(A) from tablename group by A;
结果是
湖北 4
海南 3
湖南 2不知道你要的具体效果是什么
湖北 4 其中武汉4个 武昌区有2个
海南 3 其中海口2个
union
select a&b,count(*) from tt group by a&b
union
select a&b&c,count(*) from tt group by a&b&c
union
select concat(a,b),count(*) from tt group by concat(a,b)
union
select concat(a,b,c),count(*) from tt group by concat(a,b,c)
我的要求,我重新写了结构表tb(col1,col2,col3)如下:col1 col2 col3a -1 -4
a 3 -3
b 0 -5
a 1 6
a -2 -3
b 1 1
c 3 5
a -1 -4
c 3 -3
a 0 -5
c 1 6
a -2 -3
b 1 1
a 3 5我想要的结果是:
col1 col2>0 col3>0a ? ? ?
b ? ? ?
c ? ? ?
sum(if(col3>0,1,0)) as newclo3
from tt group by col1
col1 col2>0 col3>0 a 8 3 2
b 3 2 2
c 3 3 2
sum(if(col3>0,1,0)) as newclo3
from tt group by col1unionselect count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from tt group by 1
sum(if(col3>0,1,0)) as newclo3
from tt group by col1union allselect count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from tt group by 1
你的代码
select count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1
union all
select count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd
count(*) as newclo1,
sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from tb
group by col1 with rollup;
select if(isnull(col1),'合计',col1),count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup
select nullif(col1,'合计'),count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup
select ifnull(col1,'合计'),count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup
col1 (col2==-1 or col==3) col3>0 a ? ? ?
b ? ? ?
c ? ? ? select ifnull(col1,'合计'),count(*) as newclo1,sum(if(col2==-1 or col2==-1),1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup提示出错!
col1 (col2==-1 or col2==3) col3>0 a ? ? ?
b ? ? ?
c ? ? ?
select ifnull(col1,'合计'),count(*) as newclo1,sum(if(col2==-1 or col2==-1),1,0)) as newclo2, sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup 提示出错!
sum(if(col2=-1 or col2=3,1,0)) as newclo2, sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollupmysql中没有==吧
sum(if(col2=-1 or col2=3,1,0)) as newclo2, sum(if(col3>0,1,0)) as newclo3
from ttd group by col1 with rollup
a count(a) c count(c) b count(b)
海南 1 河东区 1 三亚 1
海南 1 秀英区 1 海口 1
海南 1 龙华区 1 海口 1
湖北 2 武昌区 2 武汉 2
湖北 1 汉阳区 1 武汉 1
湖南 1 岳阳区 1 岳阳 1
湖南 1 长沙区 1 长沙 1
select count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd group by col1
union all
select count(*) as newclo1,sum(if(col2>0,1,0)) as newclo2,
sum(if(col3>0,1,0)) as newclo3
from ttd
----------
name contenta 12332
a 3434
a 234234
b 232
c 23423
c 34得出结果:name counta 3
c 2
b 1