110,571
社区成员
发帖
与我相关
我的任务
分享
Unit:
id name type
1 a 1
2 b 1
3 c 1
4 d 2
5 e 2
6 f 2
7 g 3
Type:
id name
1 aa
2 bb
3 cc
4 dd
UnitEntry:
unitID name
3 111
2 222
2 333
3 444
3 555
3 666
7 777
7 888
7 999
typeid typecount
1 6
2 0
3 3
4 0
--> 测试数据: @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 行受影响)
*/
select type.id typeid,count(*) typecount
from type
left join unit on
type.id = unit.type
left join unitentry
on unit.id = unitentry.unitID
group by type.id
UnitEntry表 Unit表
unitID ----------> id
name name Type表
typeid <------- id
name