--> 测试数据: @Unit
declare @Unit table ([id] int,[name] varchar(1),[type] int)
insert into @Unit
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'d',2 union all
select 5,'e',2 union all
select 6,'f',2 union all
select 7,'g',3
--> 测试数据: @Type
declare @Type table ([id] int,[name] varchar(2))
insert into @Type
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'
--> 测试数据: @UnitEntry
declare @UnitEntry table ([unitID] int,[name] int)
insert into @UnitEntry
select 3,111 union all
select 2,222 union all
select 2,333 union all
select 3,444 union all
select 3,555 union all
select 3,666 union all
select 7,777 union all
select 7,888 union all
select 7,999
--三表关联
select * from @Type a left join @Unit b on a.id= b.[type] left join @unitEntry c on b.id=c.unitid
--查询
select a.id as typeid,count(unitid) as typecount from @Type a left join @Unit b on a.id= b.[type] left join @unitEntry c on b.id=c.unitid
group by a.id
/*结果
id name id name type unitID name
----------- ---- ----------- ---- ----------- ----------- -----------
1 aa 1 a 1 NULL NULL
1 aa 2 b 1 2 222
1 aa 2 b 1 2 333
1 aa 3 c 1 3 111
1 aa 3 c 1 3 444
1 aa 3 c 1 3 555
1 aa 3 c 1 3 666
2 bb 4 d 2 NULL NULL
2 bb 5 e 2 NULL NULL
2 bb 6 f 2 NULL NULL
3 cc 7 g 3 7 777
3 cc 7 g 3 7 888
3 cc 7 g 3 7 999
4 dd NULL NULL NULL NULL NULL
(14 行受影响)
typeid typecount
----------- -----------
1 6
2 0
3 3
4 0
警告: 聚合或其他 SET 操作消除了空值。
(4 行受影响)
*/
