22,207
社区成员
发帖
与我相关
我的任务
分享
--测试数据
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