Join有关的几种连接查询

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Wikey_Zhang/article/details/75428412
JOIN 连接查询是SQL基础中串表查询最重要的组成部分之一,也是SQL初学者入门的一个转折点。内容很基础,但很重要!
我们常用如下几种链接方式:
1.INNER JOIN (内连接)
取的是两表之间的交集,如果把两个表的集合看成是两个圆,结果集如下图所示:
举例说明一下:
存在如下两张表
最终结果集取的是两张表共有的id的。
2.LEFT JOIN (左连接)
以左边的表为主,结果集取的是左表的全部以及右表中能在左表中关联到的部分,结果集如下:

举例说明一下:
结果集以左表为主,左表的全部内容都可以被列出,而右边只有能和左表关联上的部分才能被列出,关联不上的以null来代替
3.RIGHT JOIN (右连接)
如left join 相反,如下图:
举例说明一下:
左表关联不上的部分标记为null,和left join 相反
4.FULL JOIN (全连接)
结果集是左右表的全部内容,如下图:
举例说明一下:
左右表的全部内容都被展示出来
5.CROSS JOIN (交叉连接,很少用)
前四种链接方式都需要 接 ON 进行bool值的判断,最后一种连接方式不需要进行bool值的判断,结果集行数就是关联的两张表的行数的乘积。
举例说明一下:
结果集一共是9行,交叉连接不需要卡关联条件,直接“胡乱链接”,左表的每一行和右表的每一行做关联,不过我们可以自己加个where 条件:
这个结果集不就和inner join 的结果集一模一样的了嘛!
而且这两者的内部算法以及开销都是一模一样的:
展开阅读全文

关于FULL JOIN连接查询的问题 ̄ ̄!

09-09

现在有两个表需要用到FULL JOIN连接查询,一个表是年销售计划,就是一年12个月那个月那个部门销售某个商品多少,另一个表销售明细表,就是在某一时间,那个部门卖闻某个商品多少。下面是我的查询rnrn[code=SQL]rnselect isnull(a.syear,b.syear) as syear,rn isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,rn isnull(a.CommodityID,b.CommodityID) as CommodityIDrnfromrn(select sYear ,DepartmentID,CommodityID,rn sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,rn sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,rn sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,rn sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,rn sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,rn sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,rn sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,rn sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,rn sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,rn sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,rn sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,rn sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12rnfrom tSalePlanrngroup by sYear,DepartmentID,CommodityIDrn) as arnfull join rn(select year(sDate) as sYear ,DepartmentID,CommodityID,rn sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,rn sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,rn sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,rn sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,rn sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,rn sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,rn sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,rn sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,rn sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,rn sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,rn sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,rn sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12rnfrom tSaleOrderrngroup by year(sDate),DepartmentID,CommodityID) as brnon a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityIDrnorder by a.sYear,a.DepartmentID,a.CommodityIDrn[/code]rnrn里面的单个查询没有问题,现在FULL JOin连接查询后,SYear,DepartmentID,CommodityID这三个字段能匹配的数据没有合并,被分拆成多条数据了 ̄ ̄!不知道为什么 ̄ ̄!那个大哥帮看看 ̄ ̄!rn我另有一个DepartmentID,CommodityID这两个字段匹配的相似的查询没有问题,就这个不知道为什么不行 ̄ ̄!rn 论坛

没有更多推荐了,返回首页