联表查询的SQL语句怎么写

[aubreycanfly]

现在有a,b,c,d,e五张表。a表中的i_record_id 字段 是主键，另有字段1，字段2等，b,c,d,e中都有i_record_id外键，
大概要求是先查到a表中有多少i_record_id，再查这些i_record_id在b,c,d,e中一共出现的记录条数。
比如a表中有i_record_id 为1和2，b、c、d、e中对应i_record_id为1分别有2、2、2、3条记录，i_record_id为2分别为1、2、3、1条记录。
那么得查询结果应该是

a.字段1，a.字段2，1,9
a.字段1，a.字段2，2,7

[二月十六]

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([i_record_id] int)
Insert #a
select 1 union all
select 2
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([i_record_id] int)
Insert #b
select 1 union all
select 1 union all
select 2
GO
if not object_id(N'Tempdb..#c') is null
	drop table #c
Go
Create table #c([i_record_id] int)
Insert #c
select 1 union all
select 1 union all
select 2 union all
select 2
GO
if not object_id(N'Tempdb..#d') is null
	drop table #d
Go
Create table #d([i_record_id] int)
Insert #d
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2
GO
if not object_id(N'Tempdb..#e') is null
	drop table #e
Go
Create table #e([i_record_id] int)
Insert #e
select 1 union all
select 1 union all
select 1 union all
select 2
Go
--测试数据结束
SELECT  i_record_id ,
        i_record_id AS 字段1 ,
        ( SELECT    COUNT(1)
          FROM      #b
          WHERE     #b.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #c
          WHERE     #c.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #d
          WHERE     #d.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #e
          WHERE     #e.i_record_id = #a.i_record_id
        ) AS 字段2
FROM    #a

[aubreycanfly]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([i_record_id] int)
Insert #a
select 1 union all
select 2
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([i_record_id] int)
Insert #b
select 1 union all
select 1 union all
select 2
GO
if not object_id(N'Tempdb..#c') is null
	drop table #c
Go
Create table #c([i_record_id] int)
Insert #c
select 1 union all
select 1 union all
select 2 union all
select 2
GO
if not object_id(N'Tempdb..#d') is null
	drop table #d
Go
Create table #d([i_record_id] int)
Insert #d
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2
GO
if not object_id(N'Tempdb..#e') is null
	drop table #e
Go
Create table #e([i_record_id] int)
Insert #e
select 1 union all
select 1 union all
select 1 union all
select 2
Go
--测试数据结束
SELECT  i_record_id ,
        i_record_id AS 字段1 ,
        ( SELECT    COUNT(1)
          FROM      #b
          WHERE     #b.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #c
          WHERE     #c.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #d
          WHERE     #d.i_record_id = #a.i_record_id
        )+( SELECT    COUNT(1)
          FROM      #e
          WHERE     #e.i_record_id = #a.i_record_id
        ) AS 字段2
FROM    #a

完全没问题。谢谢~