create table ttt(a varchar(10),b varchar(10))
insert into ttt(a,b)values('100','005')
insert into ttt(a,b)values('100','005')
insert into ttt(a,b)values('100','004')
insert into ttt(a,b)values('100','100')
insert into ttt(a,b)values('100','100')insert into ttt(a,b)values('101','006')
insert into ttt(a,b)values('101','004')
insert into ttt(a,b)values('101','101')select * from ttt最后要得到的数据a, ee,ff
100,3,2
101.2,1规则为,按a分类,b为0开头的有几条数据ee,不为0开头的有几条数据ff
insert into ttt(a,b)values('100','005')
insert into ttt(a,b)values('100','005')
insert into ttt(a,b)values('100','004')
insert into ttt(a,b)values('100','100')
insert into ttt(a,b)values('100','100')insert into ttt(a,b)values('101','006')
insert into ttt(a,b)values('101','004')
insert into ttt(a,b)values('101','101')select * from ttt最后要得到的数据a, ee,ff
100,3,2
101.2,1规则为,按a分类,b为0开头的有几条数据ee,不为0开头的有几条数据ff
select a,ee=sum(case when substring(b,1,1)='0' then 1 else 0 end),
ee=sum(case when substring(b,1,1)<>'0' then 1 else 0 end)
from ttt group by a
count(case when left(b,1)=0 then 1 else 0 end) as ee,
count(case when left(b,1)<>0 then 1 else 0 end) as ff from ttt
group by a
select a ,
ee=(SELECT COUNT(1) FROM ttt WHERE LEFT(b,1)='0' AND a=t.a),
ff=(SELECT COUNT(1) FROM ttt WHERE LEFT(b,1)='1' AND a=t.a)
from ttt t GROUP BY a
/*
a ee ff
---------- ----------- -----------
100 3 2
101 2 1
*/