高手请帮忙解决一个查询问题
数据表A中有如下数据:
id zt
1 1
1 2
1 1
2 1
2 1
2 2
2 2
3 1
3 2
数据表B中有如下数据:
id mc
1 A1
2 A2
3 B
我现在需要根据输入的mc来搜索出相应mc在表A中状态为1的总条数和A中的所有条数,以A1为例,应该得到如下结果:A1 2(状态为1的) 3(所有的),我想知道怎样用一条语句实现
[解决办法]
select mc,sum(case when zt=1 then 1 end),Count(zt)
from a inner join b on a.id=b.id
where mc= 'A1 '
group by mc
[解决办法]
select b.mc,
sum(case when a.zt=1 then 1 else 0 end) as [状态为1的],
sum(1) as [所有的]
from a,b
where a.id=b.id
and b.mc= 'A1 '
group by b.mc
[解决办法]
select b.mc,
sum(case when a.zt=1 then 1 else 0 end) as [状态为1的],
count(1) as [所有的]
from a,b
where a.id=b.id
and b.mc= 'A1 '
group by b.mc
