declare @t1 table(
id int,
groupid int,
name varchar(10))
insert into @t1 (id,groupid,name)values(0,0,'A')
insert into @t1 (id,groupid,name)values(1,2,'C')
insert into @t1 (id,groupid,name)values(2,2,'B')
insert into @t1 (id,groupid,name)values(3,3,'R')
insert into @t1 (id,groupid,name)values(4,3,'R')
insert into @t1 (id,groupid,name)values(5,2,'R')
select * from @t1declare @t2 table(
groupid int,
groupname varchar(10))
insert into @t2(groupid,groupname) values(2,'GroupA')
insert into @t2(groupid,groupname) values(3,'GroupB')
insert into @t2(groupid,groupname) values(4,'GroupV')
select * from @t2-----所要的结果-----
groupid groupname count
2 GroupA 3
3 GroupB 2
4 GroupV 0
id int,
groupid int,
name varchar(10))
insert into @t1 (id,groupid,name)values(0,0,'A')
insert into @t1 (id,groupid,name)values(1,2,'C')
insert into @t1 (id,groupid,name)values(2,2,'B')
insert into @t1 (id,groupid,name)values(3,3,'R')
insert into @t1 (id,groupid,name)values(4,3,'R')
insert into @t1 (id,groupid,name)values(5,2,'R')
select * from @t1declare @t2 table(
groupid int,
groupname varchar(10))
insert into @t2(groupid,groupname) values(2,'GroupA')
insert into @t2(groupid,groupname) values(3,'GroupB')
insert into @t2(groupid,groupname) values(4,'GroupV')
select * from @t2-----所要的结果-----
groupid groupname count
2 GroupA 3
3 GroupB 2
4 GroupV 0
a.groupid,b.groupname,isnull(count(1),0) as [count]
from
@t1 a join @t2 b
on
a.groupid=b.groupid
group by
a.groupid,b.groupname
a.groupid,b.groupname,isnull(count(1),0) as [count]
from
@t1 a left join @t2 b
on
a.groupid=b.groupid
group by
a.groupid,b.groupname
上面两个语句都不行,主要是各数为0的不好统计。好象用一条语句统计不了。
select *,(select count(*) from @t1 where groupid=a.groupid) [count] from @t2 as a