select a.name,count(1),sum(decode(lx,'1类',1,0)),
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;
谢谢你,各个类型的数据已经对了可是总数不对呀,你的SQL计算出的总数是tab_b表中各个id的数据总数,如id=1的话总数就是4,而我要的结果应该是3(因为在其中有两个101它们算一个)我把你的SQL中count(1)改成count(distinct(1)) 结果全都是1.....麻烦你再看一下
sum(decode(lx,'1类',1,0)),
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;
sum(decode(lx,'1类',1,0)),
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;
你看清楚点,人家是用的count(a.name)
count(a.name)和count(tab_b.bh)如果你不是对不同值计数的话没有区别
sum(decode(lx,'1类',1,0)),
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;
select,a.name,count(1) cc,sum(decode(lx,'1类',1,0)) type1,
sum(decode(lx,'2类',1,0)) type2,
sum(decode(lx,'3类',1,0) type3)
from tab_a a,tab_b b
where a.id = b.id
group by a.name,b.bh)
group by a.name;
你没有看请我的意思。你自己实验一下SQL,看看结果是什么就知道了
sum(decode(lx,'1类',1,0)),
sum(decode(lx,'2类',1,0)),
sum(decode(lx,'3类',1,0))
from tab_a a,tab_b b
where a.id = b.id
group by a.name;count(distinct a.name) 这个语句算出来的就是个数