100分请教一个多表SQL连接，谢谢
表如下:
表1
[DClass]
ID|clan
--------
2 |aa
3 |bb
--------
表2
[dinfo]
ID|DCID|Dname
-------------
1 |2 |da
2 |2 |ds
3 |3 |de
-------------
表3
[Tindex]
ID|Tno|tdate
----------
1 |001|2013-5-5
2 |002|2013-5-6
3 |003|2013-5-5
4 |004|2013-5-6
----------
表4
[Tbody]
ID|Iid|did|tnum|tm
-------------------
1 |1 |2 |5 |10
2 |1 |3 |2 |5
3 |2 |2 |5 |8
4 |2 |3 |2 |3
5 |1 |2 |5 |10
6 |3 |3 |2 |5
7 |3 |2 |5 |8
8 |2 |3 |2 |3
-------------------
关系:dclass.id=dinfo.dcid
tindex.id=tbody.iid
tbody.did=dinfo.id
要求结果:
tdate=2013-5-5
clan=aa
dname|tnum|tm
-----------------------
da |0 |0
ds |15 |28
谢谢，100分
[解决办法]
不知道你的结果 怎么来的
dname
[解决办法]
tnum
[解决办法]
tm
-----------------------
da
[解决办法]
0
[解决办法]
0
ds
[解决办法]
15
[解决办法]
28
select dname,
tnum,
tm
from DClass dc
LEFT JOIN dinfo d
on dc.ID=d.DCID
LEFT JOIN Tbody t
on t.did=d.ID
LEFT JOIN Tindex ti
on ti.id=t.iid
where ti.tdate='2013-5-5' AND dc.clan='aa'
with DClass(ID,clan) as(
select 2,'aa' union
select 3,'bb')
,dinfo(ID,DCID,Dname) as(
select 1,2,'da' union
select 2,2,'ds' union
select 3,3,'de')
,Tindex(ID,Tno,tdate) as(
select 1,'001','2013-5-5' union
select 2,'002','2013-5-6' union
select 3,'003','2013-5-5' union
select 4,'004','2013-5-6')
,Tbody(ID,Iid,did,tnum,tm)as(
select 1 ,1 ,2 ,5 ,10 union
select 2 ,1 ,3 ,2 ,5 union
select 3 ,2 ,2 ,5 ,8 union
select 4 ,2 ,3 ,2 ,3 union
select 5 ,1 ,2 ,5 ,10 union
select 6 ,3 ,3 ,2 ,5 union
select 7 ,3 ,2 ,5 ,8 union
select 8 ,2 ,3 ,2 ,3)
select dname,isnull(sum(tnum),0),isnull(sum(tm),0) from DClass
left join dinfo on dclass.id=dinfo.dcid
left join Tbody on tbody.did=dinfo.id
left join Tindex on tindex.id=tbody.Iid
where isnull(tdate,'2013-5-5')='2013-5-5' and clan='aa'
group by dname
SELECT
b.Dname,SUM(ISNULL(c.tnum)) AS tnum,SUM(ISNULL(c.tm)) AS tm
FROM [DClass] AS a
INNER JOIN [dinfo] AS b ON a.ID=b.DCID
LEFT JOIN ([Tbody] AS c
INNER JOIN [Tindex] AS d ON d.ID=c.Iid AND d.tdate='2013-5-5') ON c.did=b.id
WHERE a.clan='aa'
GROUP BY b.Dname
