首页 新闻 搜索 专区 学院

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 | 菜鸟二级 | 园豆:388
提问于:2020-08-17 10:13
< >
分享
所有回答(3)
0

沒有where查不到資料,有where才查的到資料?

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

是,真的好奇怪

支持(0) 反对(0) dunkS | 园豆:388 (菜鸟二级) | 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 | 园豆:4273 (老鸟四级) | 2020-08-17 10:36

@RosonJ: 不行。还是空

支持(0) 反对(0) dunkS | 园豆:388 (菜鸟二级) | 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 | 园豆:4273 (老鸟四级) | 2020-08-17 10:47

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

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

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

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

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

会长 | 园豆:9555 (大侠五级) | 2020-08-17 11:01

表是临时表

支持(0) 反对(0) dunkS | 园豆:388 (菜鸟二级) | 2020-08-18 15:51
0

瞪不出问题,你删掉一部分看看

小猪猪佩奇 | 园豆:239 (菜鸟二级) | 2020-09-15 10:49
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册