34,588
社区成员
发帖
与我相关
我的任务
分享
--TB1(id,name)(主表)
--数据如下:
--1,aaa
--2,bbb
--3,ccc
--TB2(id,pid,info1)
--1,1,a11
--2,1,a12
--3,2,a21
--4,2,a22
--TB3(id,pid,info2)
--1,1,b11
--2,1,b12
--3,2,b31
--4,3,b32
--用一条SQL查询出如下结果:
--id,name,count(TB2关联记录数),count(TB3关联记录数)
--1,aaa,2,2
--2,bbb,2,1
--3,ccc,0,1
if OBJECT_ID('TB1') IS not null
drop table TB1
GO
create table TB1 (id int,name varchar(50))
insert into TB1 values(1,'aaa')
insert into TB1 values(2,'bbb')
insert into TB1 values(3,'ccc')
if OBJECT_ID('TB2') IS not null
drop table TB2
GO
create table TB2 (id int,pid int,info1 varchar(50))
insert into TB2 values(1,1,'a11')
insert into TB2 values(2,1,'a12')
insert into TB2 values(3,2,'a21')
insert into TB2 values(4,2,'a22')
if OBJECT_ID('TB3') IS not null
drop table TB3
GO
create table TB3 (id int,pid int,info2 varchar(50))
insert into TB3 values(1,1,'b11')
insert into TB3 values(2,1,'b12')
insert into TB3 values(3,2,'b31')
insert into TB3 values(4,3,'b32')
--id,name,count(TB2关联记录数),count(TB3关联记录数)
--1,aaa,2,2
--2,bbb,2,1
--3,ccc,0,1
select TB1.id,TB1.name,(select count(*) from TB2 WHERE TB1.id=TB2.pid )AS 'count(TB2关联记录数)',(select count(*) from TB3 WHERE TB1.id=TB3.pid ) AS 'count(TB3关联记录数)' from TB1
id name count(TB2关联记录数) count(TB3关联记录数)
----------- -------------------------------------------------- --------------- ---------------
1 aaa 2 2
2 bbb 2 1
3 ccc 0 1
(3 行受影响)
select tb1.ID,tb1.Name,count2,count3 from tb1
left join
(select pid,count(1) as count2 from tb2 group by pid)A on A.pid=tb1.id
left join
(select pid,count(1) as count3 from tb3 group by pid)B on B.pid=tb1.id
create table TB1
(id int, name varchar(6))
insert into TB1
select 1, 'aaa' union all
select 2, 'bbb' union all
select 3, 'ccc'
create table TB2
(id int, pid int, info1 varchar(6))
insert into TB2
select 1,1,'a11' union all
select 2,1,'a12' union all
select 3,2,'a21' union all
select 4,2,'a22'
create table TB3
(id int, pid int, info2 varchar(6))
insert into TB3
select 1,1,'b11' union all
select 2,1,'b12' union all
select 3,2,'b31' union all
select 4,3,'b32'
select id,name,
(select count(*) from TB2 where TB2.pid=TB1.id) 'TB2关联记录数',
(select count(*) from TB3 where TB3.pid=TB1.id) 'TB3关联记录数'
from TB1
id name TB2关联记录数 TB3关联记录数
----------- ------ ----------- -----------
1 aaa 2 2
2 bbb 2 1
3 ccc 0 1
(3 row(s) affected)