关于left join 的连接条件和过滤条件的关系

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_39428938/article/details/77944939

left join的困惑:一旦加上where条件,则显示的结果等于inner join 

  • 将where 换成 and  
  • 用where 是先连接然后再筛选   
  • 用and 是先筛选再连接 

过滤条件放在:
where后面:是先连接然生成临时查询结果,然后再筛选
on后面:先根据条件过滤筛选,再连 生成临时查询结果

总结:

 

  1.  对于left join,不管on后面跟什么条件,左表的数据全部查出来,因此要想过滤需把条件放到where后面

  2. 对于inner join,满足on后面的条件表的数据才能查出,可以起到过滤作用。也可以把条件放到where后面。

 

展开阅读全文

left join 条件 or的问题

06-02

[code=SQL]select *,rn (县年初实有数量+县本年增加数量-县本年减少数量) as '县年末实有数量',rn (县年初实有金额+县本年增加金额-县本年减少金额) as '县年末实有金额',rn '' as '序号' fromrn(select tbg.fgid,tbg.ID,tbg.GName as '装备项目',tbg.Prickle as '计量单位',tbg.Remark1 as '功能描述',tbg.GNo,rn sum(ISNULL(tba1.Num,0)) as '县年初实有数量',rn sum(ISNULL(tba1.Num,0)*ISNULL(tba1.Price,0)) as '县年初实有金额',rn sum(ISNULL(tba2.Num,0)) as '县本年增加数量',rn sum(ISNULL(tba2.Num,0)*ISNULL(tba2.Price,0)) as '县本年增加金额',rn sum(ISNULL(tba3.Num,0)) as '县本年减少数量',rn sum(ISNULL(tba3.Num,0)*ISNULL(tba3.Price,0)) as '县本年减少金额'rn from TB_Group tbg rn /*县年初实*/rn left join TB_Asset tba1 ON (tba1.AssetTypeID=tbg.ID or (tba1.AssetTypeID1=tbg.ID and tba1.AssetTypeID1<>465 )) and tba1.AssetStatus=0 and tba1.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba1.InputDate<=@StartTime and tba1.FundsFrom in (select ID from @tbTable) and tba1.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')rn /*增加*/rn left join TB_Asset tba2 ON (tba2.AssetTypeID=tbg.ID or (tba2.AssetTypeID1=tbg.ID and tba2.AssetTypeID1<>465 )) and tba2.AssetStatus in(0,7) and tba2.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba2.InputDate>=@StartTime and tba2.InputDate<=@EndTime and tba2.FundsFrom in (select ID from @tbTable) and tba2.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')rn /*减少*/rn left join TB_Asset tba3 ON (tba3.AssetTypeID=tbg.ID or (tba3.AssetTypeID1=tbg.ID and tba3.AssetTypeID1<>465 )) and tba3.AssetStatus=7 and tba3.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba3.InputDate>=@StartTime and tba3.InputDate<=@EndTime and tba3.FundsFrom in (select ID from @tbTable) and tba3.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')rn where tbg.IsDelete=0 and GNo like ISNULL(@gno,'')+'%' and GNo<>'1#' and GNo<>'2#' and GNO not like '2#61#%' and tbg.ID<>465 rn group by tbg.fgid,tbg.ID,tbg.GName,tbg.Prickle,tbg.Remark1,tbg.GNo) as c,f_Sort('0',0) b WHERE c.ID=b.ID ORDER BY b.sort[/code]rnrn如果left join on 里面没有or的话,查询出来的数据是没问题的,但是有or的时候就会出现问题,不知道要怎么解决 论坛

left join 条件 位置顺序

09-16

语句一:rn rn SELECTrn r.*,rn l.his_count rn FROMrn ( SELECTrn city_code,rn city_name,rn ROUTE_NAME,rn UUID,rn MAX(stat_date) AS stat_date,rn COUNT(1) AS his_count rn FROMrn FIN_JKS_CS_BDW_BL rn WHERErn 1=1 rn ANDrn STAT_DATE != ( SELECTrn MAX(stat_date) rn FROMrn FIN_JKS_CS_BDW_BLrn ) rn ANDrn CITY_CODE = '530' rn GROUP BYrn CITY_CODE,rn CITY_NAME,rn ROUTE_NAME,rn UUID rn )rn l rn LEFT JOIN ( SELECTrn * rn FROMrn FIN_JKS_CS_BDW_BL rn )rn r rn ON (l.city_name = r.city_name ANDrn l.UUID = r.UUID ANDrn l.ROUTE_NAME = r.ROUTE_NAME ANDrn l.stat_date = r.stat_date) rn ORDER BYrn r.STAT_DATE DESC rn rnrnrnrnrnrnrnrnrnrnrnrn语句二:rn rn SELECTrn r.*,rn l.his_count rn FROMrn ( SELECTrn city_code,rn city_name,rn ROUTE_NAME,rn UUID,rn MAX(stat_date) AS stat_date,rn COUNT(1) AS his_count rn FROMrn FIN_JKS_CS_BDW_BL rn WHERErn 1=1 rn ANDrn CITY_CODE = '530' rn GROUP BYrn CITY_CODE,rn CITY_NAME,rn ROUTE_NAME,rn UUID rn )rn l rn LEFT JOIN ( SELECTrn * rn FROMrn FIN_JKS_CS_BDW_BL rn )rn r rn ON (l.city_name = r.city_name ANDrn l.UUID = r.UUID ANDrn l.ROUTE_NAME = r.ROUTE_NAME ANDrn l.stat_date = r.stat_date) rn WHERErn r.STAT_DATE != ( SELECTrn MAX(stat_date) rn FROMrn FIN_JKS_CS_BDW_BLrn ) rn ORDER BYrn r.STAT_DATE DESC rnrnrnrnrnrnrn求解,语句一和语句二,是条件rn STAT_DATE != ( SELECTrn MAX(stat_date) rn FROMrn FIN_JKS_CS_BDW_BLrn ) rn放的位置不同,一个放left join左边,一个放on 右边,出来结果不相同,语句一出现了429条,语句二出现了419条,请问这是为什么啊。。。rnrn求详细解答。rn 论坛

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