现在有三张表Unit、UnitEntry和TypeUnit:
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我想通过一条SQL语句,查询出结果如下:typeid typecount
1 6
2 0
3 3
4 0请高手帮忙写下,谢谢
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我想通过一条SQL语句,查询出结果如下:typeid typecount
1 6
2 0
3 3
4 0请高手帮忙写下,谢谢
Unit inner join Type
on Unit.type = Type.id
group by Type.id不会是这么简单的问题吧?汗..lz把问题说清楚些
typecount是我想查到的结果表中的关联如下:UnitEntry表 Unit表
unitID ----------> id
name name Type表
typeid <------- id
name
from type
left join unit on
type.id = unit.type
left join unitentry
on unit.id = unitentry.unitID
group by type.id
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 行受影响)*/
貌似我自己弄出来了,哈哈,不过看了wxg22526451回复,我又想了个方法