SQL日期范围查询失效求助：LEFT JOIN返回全量历史数据

咱们来拆解下你遇到的这个问题——核心是你踩了LEFT JOIN中ON子句条件和WHERE子句条件区别的常见坑，导致过滤逻辑不符合预期。

问题根源分析 #

你当前把stkhd.sth_tran_date的范围限制放在了LEFT JOIN skm_stocktransfer_hdr的ON子句里，这只会影响右表（stkhd）和左表（通过stkdl关联的intvw）的匹配逻辑：只有stkhd中符合日期条件的记录才会和左表关联，但左表（intvw）的所有行都会被保留——哪怕某行intvw根本找不到符合日期的stkhd记录，甚至连对应的stkhd记录都没有，这行intvw数据依然会出现在结果里（对应的stkhd字段为NULL）。

当你保留intvw.Tran_no = 'R0000085590'时，刚好这个单号在stkhd里有符合日期的匹配，所以结果看起来正常；但注释掉这个单号条件后，所有intvw的历史数据都被LEFT JOIN保留了下来，这就是你看到全部历史数据的原因。

另外你注释掉的WHERE子句里的日期条件，本来是可以过滤掉这些无匹配的行的，但你把它注释了，这就导致没有全局的日期过滤逻辑。

针对性解决方案 #

根据你的需求，分两种场景给出解决办法：

场景1：只需要保留有符合日期的stkhd记录的intvw数据

把日期条件从JOIN的ON子句移到WHERE子句，这样就能全局过滤所有不符合日期的行（包括左表中没有对应stkhd日期匹配的行）：

SELECT intvw.Tran_no RequestNo, stkdl.std_tran_no RamcoTranNo, stkhd.sth_tran_date TranDate, 
       intvw.Bg_Code ItemCode, intvw.reqdqty ReqQty, stkdl.std_tran_quantity TransferQty, 
       stkdl.std_from_lot_no, (intvw.reqdqty-stkdl.std_tran_quantity) * -1 Varience, 
       intvw.From_Wh_Code FromWH, intvw.To_Wh_Code ToWH 
FROM res_his_interface_stock_vw intvw 
LEFT JOIN skm_stocktransfer_dtl stkdl ON intvw.Tran_no = stkdl.std_tran_no 
                                      AND intvw.Bg_Code = stkdl.std_item_code 
                                      AND intvw.From_Wh_Code = stkdl.std_from_wh_code 
LEFT JOIN skm_stocktransfer_hdr stkhd ON stkhd.sth_tran_no = stkdl.std_tran_no 
                                      AND stkhd.sth_status = 'AU' -- 状态条件属于关联匹配逻辑，保留在ON里
WHERE intvw.From_Wh_Code = 'MAINSTRS' 
  -- 日期条件移到WHERE，作为全局过滤
  AND stkhd.sth_tran_date BETWEEN '2020-01-19 00:00:00.000' AND '2020-01-19 00:00:00.000' 
-- AND intvw.Tran_no = 'R0000085590' -- 注释后也能按日期范围返回数据
ORDER BY 1 DESC 

如果明确只需要有stkhd匹配的行，也可以直接把LEFT JOIN改成INNER JOIN，逻辑更清晰：

SELECT intvw.Tran_no RequestNo, stkdl.std_tran_no RamcoTranNo, stkhd.sth_tran_date TranDate, 
       intvw.Bg_Code ItemCode, intvw.reqdqty ReqQty, stkdl.std_tran_quantity TransferQty, 
       stkdl.std_from_lot_no, (intvw.reqdqty-stkdl.std_tran_quantity) * -1 Varience, 
       intvw.From_Wh_Code FromWH, intvw.To_Wh_Code ToWH 
FROM res_his_interface_stock_vw intvw 
INNER JOIN skm_stocktransfer_dtl stkdl ON intvw.Tran_no = stkdl.std_tran_no 
                                      AND intvw.Bg_Code = stkdl.std_item_code 
                                      AND intvw.From_Wh_Code = stkdl.std_from_wh_code 
INNER JOIN skm_stocktransfer_hdr stkhd ON stkhd.sth_tran_no = stkdl.std_tran_no 
                                      AND stkhd.sth_status = 'AU' 
                                      AND stkhd.sth_tran_date BETWEEN '2020-01-19 00:00:00.000' AND '2020-01-19 00:00:00.000'
WHERE intvw.From_Wh_Code = 'MAINSTRS' 
-- AND intvw.Tran_no = 'R0000085590'
ORDER BY 1 DESC 

场景2：需要保留所有intvw行，但仅关联符合日期的stkhd记录

如果你希望保留所有intvw的行，只是在关联stkhd时只匹配符合日期的记录（无匹配的行stkhd字段显示NULL），那可以保留ON子句里的日期条件，但同时需要确保intvw本身的业务逻辑和日期相关（比如intvw有自己的创建日期字段），在WHERE子句里过滤intvw的日期：

SELECT intvw.Tran_no RequestNo, stkdl.std_tran_no RamcoTranNo, stkhd.sth_tran_date TranDate, 
       intvw.Bg_Code ItemCode, intvw.reqdqty ReqQty, stkdl.std_tran_quantity TransferQty, 
       stkdl.std_from_lot_no, (intvw.reqdqty-stkdl.std_tran_quantity) * -1 Varience, 
       intvw.From_Wh_Code FromWH, intvw.To_Wh_Code ToWH 
FROM res_his_interface_stock_vw intvw 
LEFT JOIN skm_stocktransfer_dtl stkdl ON intvw.Tran_no = stkdl.std_tran_no 
                                      AND intvw.Bg_Code = stkdl.std_item_code 
                                      AND intvw.From_Wh_Code = stkdl.std_from_wh_code 
LEFT JOIN skm_stocktransfer_hdr stkhd ON stkhd.sth_tran_no = stkdl.std_tran_no 
                                      AND stkhd.sth_status = 'AU' 
                                      AND stkhd.sth_tran_date BETWEEN '2020-01-19 00:00:00.000' AND '2020-01-19 00:00:00.000'
WHERE intvw.From_Wh_Code = 'MAINSTRS' 
  -- 假设intvw有自己的日期字段，比如intvw.tran_date，过滤intvw的日期范围
  AND intvw.tran_date BETWEEN '2020-01-19 00:00:00.000' AND '2020-01-19 23:59:59.999' 
-- AND intvw.Tran_no = 'R0000085590'
ORDER BY 1 DESC 

关键知识点总结 #

• ON子句：用于定义JOIN的关联匹配规则，不会排除左表（LEFT JOIN的左表）的行，只会影响右表哪些行被关联进来
• WHERE子句：用于全局过滤所有结果行，不管是左表还是右表的行，不满足条件的都会被排除
• 当你在WHERE子句中过滤右表的字段时，LEFT JOIN会自动等价于INNER JOIN，因为NULL值无法满足任何非IS NULL的比较条件

内容的提问来源于stack exchange，提问作者Saifuddin