关于主从表统计问题
create table aa
(
id int IDENTITY (1, 1) NOT NULL,
title varchar(20),
kk varchar(20)
)
create table bb
(
id int IDENTITY (1, 1) NOT NULL,
kk varchar(20),
singe int
)
insert into aa values('第一个','a')
insert into aa values('第二个','b')
insert into bb values('a',1)
insert into bb values('a',1)
insert into bb values('a',0)
insert into bb values('a',0)
insert into bb values('a',1)
insert into bb values('b',1)
insert into bb values('b',0)
insert into bb values('b',1)
insert into bb values('b',0)
insert into bb values('b',0)
如果想生成如下的统计表格
title 全部 等于0
第一个 5 2
第二个 5 3
如果想这样弄个咋整？
[最优解释]
select title,
sum(case when singe is not null then 1 else 0 end) 全部,
sum(case when singe=0 then 1 else 0 end) 等于0
from aa,bb
where aa.kk=bb.kk
group by title
select aa.title,
count(bb.*) as '全部',
sum(case when bb.single =0 then 1 else 0 end) as '等于0'
from aa right join bb on aa.kk=bb.kk
grou by aa.title
SELECT
title,COUNT(TITLE),SUM(CASE BB.SINGE WHEN 0 THEN 1 ELSE 0 END)
FROM
aa LEFT JOIN BB ON AA.kk=BB.KK GROUP BY TITLE
