left join on 多表同表不同条件

2011-12-19 11:36:54 5.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【机器学习】实验3，贝叶斯垃圾邮件识别（完整代码实现＋报告）电子邮件是互联网的一项重要服务，在大家的学习、工作和生活中会广泛使用。但是大家的邮箱常常被各种各样的垃圾邮件填充了。有统计显示，每天互联网上产生的垃圾邮件有几百亿近千亿的量级。因此，对电子邮件服务提供商来说，垃圾邮件过滤是一项重要功能。而朴素贝叶斯算法在垃圾邮件识别任务上一直表现非常好，至今仍然有很多系统在使用朴素贝叶斯算法作为基本的垃圾邮件识别算法。本次实验数据集来自的中文垃圾邮件数据集，目录解压后包含三个文件夹，其中data目录下是所有的邮件（未分词），已分词好的邮件在data_cut目录下。阅读详情

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倍，请指点一下。

...全文

【水下小目标检测】SWIPENet与样本重加权：应对模糊与噪声的深度学习策略本文深入探讨了水下小目标检测的挑战与解决方案，重点介绍了SWIPENet网络架构与IMA样本重加权算法。通过空洞卷积模块、特征金字塔重构和多尺度预测机制，SWIPENet有效提升了模糊目标的检测精度。结合Invert Multi-Class Adaboost算法，系统性地解决了水下环境中的样本不均衡问题，在URPC数据集上实现了48.8%的mAP，特别适合海洋生物检测等应用场景。阅读详情

cn6177770

ASP 28,401 成员 356,940 内容

+ 关注 + 加入社区

切换为时间正序

Dogfish 2011-12-21

打赏
举报

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

kris2010 2011-12-21

打赏
举报

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

cn6177770 2011-12-21

打赏
举报

没人看帖啊！

cn6177770 2011-12-21

打赏
举报

问题已解决，感谢晴天的提示：
分析：我开始的代码，第二个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 2011-12-21

打赏
举报



	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' 与查询中所用的表名或别名不匹配。

kris2010 2011-12-21

打赏
举报

 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 2011-12-21

打赏
举报

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

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

-晴天 2011-12-19

打赏
举报

两种解决办法:
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 2011-12-19

打赏
举报

在线等待！请高手指点！

cn6177770 2011-12-19

打赏
举报

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

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中可以根据不同的条件求和，然后一次性输出！请大家帮帮忙！

left join on 多表同表不同条件

相关推荐