首页 新闻 会员 周边

sql 为啥不加where 为空，加where 就不为空

0
悬赏园豆：5 [待解决问题]

SELECT A.OrderID,B.OrderID,B.MagLotID,C.Lot , C.LotType, C.Process,
d.TubeSN,c.DetectorSN , D.Process
FROM #MES_Device_Order A left JOIN MES_Device_Order_MagLot B ON A.OrderID=B.OrderID
INNER JOIN MES_QJ_Mag_Lot_Online_Details C ON B.MagLotID=C.Lot AND C.LotType='MagLotID' AND C.Process IN ('QJ211')
inner join MES_Device_Trouble_Record D on d.TubeSN=c.DetectorSN and D.Process like'Q%'

SELECT A.OrderID,B.OrderID,B.MagLotID,C.Lot , C.LotType, C.Process,
d.TubeSN,c.DetectorSN , D.Process
FROM #MES_Device_Order A left JOIN MES_Device_Order_MagLot B ON A.OrderID=B.OrderID
INNER JOIN MES_QJ_Mag_Lot_Online_Details C ON B.MagLotID=C.Lot AND C.LotType='MagLotID' AND C.Process IN ('QJ211')
inner join MES_Device_Trouble_Record D on d.TubeSN=c.DetectorSN and D.Process like'Q%'
where d.TubeSN ='2150191335'

sql
dunkS的主页 dunkS | 菜鸟二级 | 园豆：382
提问于：2020-08-17 10:13
< >
分享
所有回答(2)
0

沒有where查不到資料，有where才查的到資料？

RosonJ | 园豆：4910 (老鸟四级) | 2020-08-17 10:18

是，真的好奇怪

支持(0) 反对(0) dunkS | 园豆：382 (菜鸟二级) | 2020-08-17 10:26

@dunkS:

SELECT
    A.OrderID,
    B.OrderID,
    B.MagLotID,
    C.Lot,
    C.LotType,
    C.Process,
    D.TubeSN,
    C.DetectorSN,
    D.Process
FROM #MES_Device_Order A 
LEFT JOIN MES_Device_Order_MagLot B ON A.OrderID = B.OrderID
JOIN MES_QJ_Mag_Lot_Online_Details C ON B.MagLotID = C.Lot AND C.LotType = 'MagLotID' AND C.Process IN ('QJ211')
JOIN MES_Device_Trouble_Record D ON C.DetectorSN = D.TubeSN and D.Process LIKE'Q%'
WHERE 1 = 1

試試這個版本

支持(0) 反对(0) RosonJ | 园豆：4910 (老鸟四级) | 2020-08-17 10:36

@RosonJ: 不行。还是空

支持(0) 反对(0) dunkS | 园豆：382 (菜鸟二级) | 2020-08-17 10:44

@dunkS:
也就是說"D.TubeSN = '2150191335'"這個條件才是關鍵
我猜問題可能出在"C.DetectorSN = D.TubeSN"這個ON條件上
同樣我提供的語法

JOIN MES_Device_Trouble_Record D ON C.DetectorSN = D.TubeSN and D.Process LIKE 'Q%'

改成

LEFT JOIN MES_Device_Trouble_Record D ON C.DetectorSN = D.TubeSN and D.Process LIKE 'Q%'

試試

支持(0) 反对(0) RosonJ | 园豆：4910 (老鸟四级) | 2020-08-17 10:47

@RosonJ: 我把这个表放前面好了，不知道什么原因，left join 试过，不行

支持(0) 反对(0) dunkS | 园豆：382 (菜鸟二级) | 2020-08-17 10:53

@dunkS:
你的JOIN順序應該動不了
MES_QJ_Mag_Lot_Online_Details.DetectorSN
MES_Device_Trouble_Record.TubeSN
試著確認這兩個欄位資料是否有問題吧

支持(0) 反对(0) RosonJ | 园豆：4910 (老鸟四级) | 2020-08-17 10:58
0

不科学阿，你能把建表语句，测试数据和SQL都发上来看看吗

会长 | 园豆：12401 (专家六级) | 2020-08-17 11:01

表是临时表

支持(0) 反对(0) dunkS | 园豆：382 (菜鸟二级) | 2020-08-18 15:51
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。