left join on 多表同表不同条件

[cn6177770]

sql="select a.ID as DID,"
	sql=sql&" IsNull(sum(b.Signing_AmountIN),0) as q1a,"
	sql=sql&" IsNull(sum(c.Signing_AmountIN),0) as q2a"
	sql=sql&" from KL_Depart as a"
	
	sql=sql&" left join KL_Signing b"
	sql=sql&" on b.DepartID = a.ID"
	sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
	
	sql=sql&" left join KL_Signing c"
	sql=sql&" on c.DepartID = a.ID"
	sql=sql&" and c.Signing_Stage = 3 and c.UnitID = "&KL_UnitID&" and c.ISCheckD = 1 and c.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"

	
	sql=sql&" where a.UnitID  ="&KL_UnitID&" and a.ISWriteOFF = 0"
	sql=sql&" group by a.ID"
	sql=sql&" order by q1a desc"

如果我只Left join 一个的话，结果是正确的，但是我再增加了一个Left Join的话，结果q1a是正确结果的2倍，q2a是正确结果的3倍，请指点一下。

[cn6177770]

不好意思！能否按照我的代码详细给点提示咯！我按照你写的第一种方法

sql="select * from("
sql=sql&" select a.ID as DID,"
sql=sql&" IsNull(sum(b.Signing_AmountIN),0) as q1a,"
sql=sql&" from KL_Depart as a"

sql=sql&" left join KL_Signing b"
sql=sql&" on b.DepartID = a.ID"
sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
sql=sql&" where a.UnitID ="&KL_UnitID&" and a.ISWriteOFF = 0"
sql=sql&" ) as aa inner join ("

sql=sql&" select a.ID as DID,"
sql=sql&" IsNull(sum(b.Signing_AmountIN),0) as q2a"
sql=sql&" from KL_Depart as a"

sql=sql&" left join KL_Signing b"
sql=sql&" on b.DepartID = a.ID"
sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate1)&"' and '"&CDate(BDate2)&"'"
sql=sql&" where a.UnitID ="&KL_UnitID&" and a.ISWriteOFF = 0"
sql=sql&" ) as bb"

sql=sql&" on bb.DepartID = aa.DepartID"

sql=sql&" group by aa.DID"
sql=sql&" order by aa.q1a desc"

还是不行，因为我最终的目的不是3个表，而是很多表。
我要做的事查询一个表格：

部门 全年总金额 本月金额 全年成本 本月成本 全年... 本月...

这么一张表 ，有很多项目，但求一条SQL语句求出，数据表中KL_Signing中可以根据不同的条件求和，然后一次性输出！请大家帮帮忙！

[cn6177770]

在线等待！请高手指点！

[-晴天]

两种解决办法:
1.先将表两两连接,然后再将连接结果连接:
select * from(
select ... from .. a left join ... b on a.id=b.id
)a inner join (
select ... from ...a left join ... b on a.id=b.id
)b on a.id=b.id
2.只用一个连接,而另一个在select 子句中用子查询获得:
select .....,(select xx from c where id=a.id) as xx
from .... a left join ... b on a.id=b.id

[cn6177770]

[Quote=引用 5 楼 kris2010 的回复:]

你要用inner join, left 会把左边的都列出来, 即使是不匹配的.
[/Quote]
左边的我就是都要列出来，因为我用了Isnull(xxx,0),没有匹配的，输出为0。当我只用了一个Left Join，结果是准确的！

[kris2010]

 sql=sql&" left join KL_Signing b"
    sql=sql&" on b.DepartID = a.ID"
    sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
    
    sql=sql&" left join KL_Signing c"
    sql=sql&" on c.DepartID = a.ID"
    sql=sql&" and c.Signing_Stage = 3 and c.UnitID = "&KL_UnitID&" and c.ISCheckD = 1 and c.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"

sql=sql&" on b.DepartID = a.ID"
    sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"

这部分放在上边代码的后面试下.

[cn6177770]

	sql="select a.ID as DID,"
	sql=sql&" IsNull(sum(b.Signing_AmountIN),0) as q1a,"
	sql=sql&" IsNull(sum(c.Signing_AmountIN),0) as q2a"
	sql=sql&" from KL_Depart as a"
	
	sql=sql&" left join KL_Signing b"
	sql=sql&" left join KL_Signing c"
	
	sql=sql&" on b.DepartID = a.ID"
	sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
	sql=sql&" on c.DepartID = a.ID"
	sql=sql&" and c.Signing_Stage = 3 and c.UnitID = "&KL_UnitID&" and c.ISCheckD = 1 and c.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
	
	sql=sql&" where a.UnitID  ="&KL_UnitID&" and a.ISWriteOFF = 0"
	sql=sql&" group by a.ID"
	sql=sql&" order by q1a desc"

这样的话提示错误：

Microsoft OLE DB Provider for SQL Server 错误 '80040e14'
列前缀 'a' 与查询中所用的表名或别名不匹配。

[cn6177770]

问题已解决，感谢晴天的提示：
分析：我开始的代码，第二个Left Join on在连接时是将前面的查询语句当做一个整体来联合，因为前面的查询已经查询出了 部门 的数据，因此，二次联合时查询的 结果肯定又要sum前面的结果，所以出现了2倍、3倍的情况。

解决思路：
把需要的数据单独查询，结果集作为一个表，然后与部门联合查询，就可以了！

	sql="select aa.ID as DID,"
	sql=sql&" bb.q1a,"
	sql=sql&" cc.q2a,"
	sql=sql&" dd.q3a"
	sql=sql&" from KL_Depart as aa,"
	
	sql=sql&" (select a.ID as bID,"
	sql=sql&" IsNull(sum(b.Signing_AmountIN),0) as q1a"
	sql=sql&" from KL_Depart as a"
	sql=sql&" left join KL_Signing b"
	sql=sql&" on b.DepartID = a.ID"
	sql=sql&" and b.Signing_Stage = 3 and b.UnitID = "&KL_UnitID&" and b.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
	sql=sql&" where a.UnitID  ="&KL_UnitID&" and a.ISWriteOFF = 0"
	sql=sql&" group by a.ID"
	sql=sql&" ) as bb,"
	
	sql=sql&" (select a.ID as cID,"
	sql=sql&" IsNull(sum(c.Signing_AmountIN),0) as q2a"
	sql=sql&" from KL_Depart as a"
	sql=sql&" left join KL_Signing c"
	sql=sql&" on c.DepartID = a.ID"
	sql=sql&" and c.Signing_Stage = 3 and c.UnitID = "&KL_UnitID&" and c.ISCheckD = 1 and c.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"'"
	sql=sql&" where a.UnitID  ="&KL_UnitID&" and a.ISWriteOFF = 0"
	sql=sql&" group by a.ID"
	sql=sql&" ) as cc,"

	sql=sql&" (select a.ID as dID,"
	sql=sql&" IsNull(sum(d.Signing_AmountIN),0) as q3a"
	sql=sql&" from KL_Depart as a"
	sql=sql&" left join KL_Signing d"
	sql=sql&" on d.DepartID = a.ID"
	sql=sql&" and d.Signing_Stage = 3 and d.UnitID = "&KL_UnitID&" and d.ISCheckD = 1 and d.Signing_UseDate between '"&CDate(BDate)&"' and '"&CDate(BDate2)&"' and Signing_Units in (select ResourceID from KL_PurchaseContract where UnitID = "&KL_UnitID&")"
	sql=sql&" where a.UnitID  ="&KL_UnitID&" and a.ISWriteOFF = 0"
	sql=sql&" group by a.ID"
	sql=sql&" ) as dd"

	sql=sql&" where aa.UnitID  ="&KL_UnitID&" and aa.ISWriteOFF = 0 and bb.bID=aa.ID and cc.cID=aa.ID and dd.dID=aa.ID"
	
	sql=sql&" order by q1a desc"

[cn6177770]

没人看帖啊！

[kris2010]

你要用inner join, left 会把左边的都列出来, 即使是不匹配的.

[Dogfish]

是符合条件的记录匹配就出来，所以才又你这个效果。应该在on条件里加多点限制。在where是不行得。