select a.Aid,b.Aname,count(*) Acount from 表B a,表A b where a.Aid=b.Aid group by a.Aid,b.Aname
select A.aid, A.bname, count(b.*) as Acount from A,B WHERE a.aid=b.bid and a.aid='1' group by a.aid
select aid=aid, aname=max(aname),Acount=count(1) from B group by aid
select aid=aid, aname=max(aname),Acount=count(1) from B group by aid
select aid,aname,Acount=count(*) from B表 group by aid,aname where aid=1
--上面写反了. select aid,aname,Acount=count(*) from B表 where aid=1 group by aid,aname
--下面是测试 select aid,aname,acount=count(*) from( select Bid=1,Bname='a',Aid=1,Aname='aa' union all select 2,'b',1,'aa' union all select 3,'c',1,'aa' union all select 3,'d',2,'bb' ) b where aid=1 group by aid,aname/*--测试结果 aid aname acount ----------- ----- ----------- 1 aa 3(所影响的行数为 1 行)--*/
declare @Aid select Aid,Aname,count(Aid) as Acount from B join A on B.Aid=A.Aid and A.Aid=@Aid
create table aa (aid int,aname varchar(50)) create table bb (bid int,bname varchar(50),aid int) insert into aa values (1,'aa') insert into aa values (2,'bb') insert into aa values (3,'cc') insert into bb values (1,'a',1) insert into bb values (2,'b',1) insert into bb values (3,'c',1) insert into bb values (4,'d',2) insert into bb values (5,'e',2) insert into bb values (6,'f',3) select a.Aid,b.Aname,count(*) Acount from bb a,aa b where a.Aid=b.Aid group by a.Aid,b.Aname --测试结果如下 1 aa 3 2 bb 2 3 cc 1
where a.Aid=b.Aid group by a.Aid,b.Aname
where aid=1
select aid,aname,Acount=count(*) from B表
where aid=1
group by aid,aname
select aid,aname,acount=count(*) from(
select Bid=1,Bname='a',Aid=1,Aname='aa'
union all select 2,'b',1,'aa'
union all select 3,'c',1,'aa'
union all select 3,'d',2,'bb'
) b
where aid=1
group by aid,aname/*--测试结果
aid aname acount
----------- ----- -----------
1 aa 3(所影响的行数为 1 行)--*/
select Aid,Aname,count(Aid) as Acount
from B join A on B.Aid=A.Aid and A.Aid=@Aid
create table bb (bid int,bname varchar(50),aid int)
insert into aa values (1,'aa')
insert into aa values (2,'bb')
insert into aa values (3,'cc')
insert into bb values (1,'a',1)
insert into bb values (2,'b',1)
insert into bb values (3,'c',1)
insert into bb values (4,'d',2)
insert into bb values (5,'e',2)
insert into bb values (6,'f',3)
select a.Aid,b.Aname,count(*) Acount from bb a,aa b where a.Aid=b.Aid group by a.Aid,b.Aname
--测试结果如下
1 aa 3
2 bb 2
3 cc 1