现要得到如下数据:(把表二中数据附加到表一中)
Id name num tableTwoNum
1 a 11 0
2 b 22 22
3 b 33 0
礼拜天结账!
...全文
3113打赏收藏
簡單的兩表! 礼拜天结账!
现有两表,字段如下: 表一: Id name num 1 a 11 2 b 22 3 b 33 表二: Id parentID 12 表二的parentID关联表一的Id 现要得到如下数据:(把表二中数据附加到表一中) Id name num tableTwoNum 1 a 11 0 2 b 22 22 3 b 33 0 礼拜天结账!
select a.*,
(case when b.id is null then 0 else a.num end ) as tableTwoNum,
(case when b.id is null then 0 else a.num end )*
(select count(*) from #table3 where secondtableid=b.id) as tableThreeNum
from #table1 a left join #table2 b on a.id=b.parentid
example
table2
Id parentID
1 2
2 1
3 2
insert table3 select 4, 3, 2 select 5, 3, 3
----
Id name num tableTwoNum tableThreeNum
1 a 11 11 11
2 b 22 22 66
2 b 22 22 22
3 b 33 0 0
是不是要得到这样的结果
Id name num tableTwoNum tableThreeNum
1 a 11 11 00
2 b 22 22 66
2 b 22 22 22 --表1 id为2的记录数量相加
3 c 33 0 0
即
Id name num tableTwoNum tableThreeNum
1 a 11 11 0
2 b 22 22 88
3 c 33 0 0
改为这样
select c.id,c.name,c.num,c.tabletwonum,c.tabletwonum*count(d.secondtableid)
as tableThreeNum
from
(select a.*,isnull(b.id,0) bid ,(case when b.id is null then 0 else a.num end ) as tableTwoNum from t1 a left join t2 b on a.id=b.parentid) c
left join t3 d
on d.secondtableid=c.bid
group by c.id,c.name,c.num,c.tabletwonum