我的left join失效了，用了left join后跟没用一样

[钛元素]

SELECT A.LCBH AS [流程编号], A.SQRQ AS [申请日期], C.lastname AS [申请人], D.departmentname AS [所属部门], A.ccsy AS [报销事由], 
E.subcompanyname AS [付款单位], A.ccksrq AS [出差开始日期], A.ccjsrq AS [出差结束日期], 
F.name AS [预算科目], G.name AS [研发其他选项], 
CASE B.cdztlx WHEN 0 THEN '个人' WHEN 1 THEN '部门'  WHEN 2 THEN '分部'  WHEN 3 THEN '成本中心'  END  AS [承担主体类型],
H.departmentname AS [承担主体], B.fyrq AS [费用日期], B.qqdd AS [起讫地点], B.ts AS [天数], B.fjp AS [飞机票], 
B.qcp AS [汽车票], B.hcp AS [火车票], B.zsf AS [住宿费], B.zdf AS [招待费], B.hsbt AS [伙食补贴],
B.jtbt AS [交通补贴], B.qt AS [其他], B.bxjexj AS [报销金额小计], B.zy AS [摘要], B.sbje AS [实报金额]
FROM formtable_main_101 A
LEFT JOIN formtable_main_101_dt1 B ON A.id = B.mainid
LEFT JOIN HrmResource C ON A.sqr = C.id
LEFT JOIN HrmDepartment D ON A.ssbm = D.id 
LEFT JOIN hrmsubcompany E ON A.fkdw = E.id
LEFT JOIN fnabudgetfeetype F ON B.yskm = F.id
LEFT JOIN mode_selectitempagedetail G ON B.yfqtxx = G.disorder
LEFT JOIN HrmDepartment H ON B.cdzt = H.id

WHERE F.archive <> 1 AND G.mianid = 89

就是红色部分，去掉就显示700多条，加上就只有55条，跟join一样，left失效了。help！

 

 

[钛元素]

搜到了，谢谢 https://blog.csdn.net/foosir/article/details/122517387?spm=1035.2023.3001.6557&utm_medium=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~OPENSEARCH~Rate-4-122517387-bbs-619418115.264^v3^pc_relevant_bbs_down_v2_opensearchbbsnew&depth_1-utm_source=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~OPENSEARCH~Rate-4-122517387-bbs-619418115.264^v3^pc_relevant_bbs_down_v2_opensearchbbsnew

[二月十六]

left join 后on的条件不会减少数据，可能数据会变多，比如
id
1

id
1
2
1

这样关联会出来两条数据，但是如果在where条件里写了t2 的字段条件，就可能会出现数据变少的情况，比如t2.id=2 这样的话，一条数据也出不来了

[吉普赛的歌]

你那个博客有个结论是不对的。
left join 的第2个条件也是起作用的。

declare @ta table(
    aId int primary key,
    bId int not null,
    an nvarchar(10)
)
declare @tb table(
    bId int primary key,
    bn nvarchar(10)
)
insert into @ta(aId,bId,an) values(1,1,'a')
insert into @ta(aId,bId,an) values(2,1,'b')
insert into @ta(aId,bId,an) values(3,1,'c')
insert into @ta(aId,bId,an) values(4,2,'c')
--
insert into @tb(bId,bn) values(1,'bb')
insert into @tb(bId,bn) values(2,'bc')
--------- 以上为测试数据 ------------

--left join , 1个条件关联
select * from @ta as a left join @tb as b 
    on a.bid=b.bid 
/*
aId    bId    an    bId    bn
1    1    a    1    bb
2    1    b    1    bb
3    1    c    1    bb
4    2    c    2    bc
*/

--left join , 2个条件关联， 第2个条件也起作用了
select * from @ta as a left join @tb as b 
    on a.bid=b.bid     and b.bn='bc'
/*
aId    bId    an    bId        bn
1    1    a    NULL    NULL
2    1    b    NULL    NULL
3    1    c    NULL    NULL
4    2    c    2        bc
*/

但具体到你的这个问题，是因为 where 后面加了右表的条件：G.mianid = 89