现有两张表:
1,表1 id category
1 1类别
2 2类别
3 3类别
4 4类别
5 5类别2,表2 f1 f2 category f4
1 aaa 1类别 ds
1 aaa 2类别 ds
1 aaa 3类别 ds
1 aaa 4类别 ds
1 aaa 3类别 ds
1 aaa 1类别 ds
1 aaa 1类别 ds
现在想统计如下效果:
id category num
1 1类别 3
2 2类别 1
3 3类别 2
4 4类别 1
5 5类别 5请高手给一个存储过程或select语句,谢谢!
1,表1 id category
1 1类别
2 2类别
3 3类别
4 4类别
5 5类别2,表2 f1 f2 category f4
1 aaa 1类别 ds
1 aaa 2类别 ds
1 aaa 3类别 ds
1 aaa 4类别 ds
1 aaa 3类别 ds
1 aaa 1类别 ds
1 aaa 1类别 ds
现在想统计如下效果:
id category num
1 1类别 3
2 2类别 1
3 3类别 2
4 4类别 1
5 5类别 5请高手给一个存储过程或select语句,谢谢!
insert into tb1 values(1 ,'1类别')
insert into tb1 values(2 ,'2类别')
insert into tb1 values(3 ,'3类别')
insert into tb1 values(4 ,'4类别')
insert into tb1 values(5 ,'5类别')
create table tb2(f1 int,f2 varchar(10),category varchar(10),f4 varchar(10))
insert into tb2 values(1 ,'aaa' ,'1类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'2类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'3类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'4类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'3类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'1类别' ,'ds')
insert into tb2 values(1 ,'aaa' ,'1类别' ,'ds')
goselect a.* , isnull(count(b.category),0) num from tb1 a left join tb2 b on a.category = b.category group by a.id , a.category drop table tb1 , tb2/*
id category num
----------- ---------- -----------
1 1类别 3
2 2类别 1
3 3类别 2
4 4类别 1
5 5类别 0(所影响的行数为 5 行)
*/
a.* ,
isnull(count(b.category),0) num
from
tb1 a
left join
tb2 b
on
a.category = b.category
group by
a.id , a.category
order by
a.id
as
begin
select a.*,isnull(count(b.category),0) num
from tb1 a
left join tb2 b
on a.category=b.category
group by a.id,a.category
end