我正在尝试寻找一些帮助来解决这个函数。我正在尝试列出oL表中包含特定值的rL表中不存在的所有SERIAL_NUMBER项。
下面是我想出的代码,但没有更进一步。不管我怎么写,我总是生成所有存在的或根本没有的SERIAL_NUMBER项。
DECLARE @val int
SET @val = 43770
SELECT oL.SERIAL_NUMBER
FROM
ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL
LEFT JOIN ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL
ON oL.SERIAL_NUMBER != rL.SERIAL_NUMBER
WHERE oL.SO_LINE_NUMBER = @val;我到底做错了什么?这段代码生成所有的结果,其中oL.SO_LINE_NUMBER = @val。我需要它--减去rL表中已经存在的序列号。
我也尝试过添加
WHERE rl.SERIAL_NUMBER IS NULL但这不会产生任何结果。
发布于 2017-01-20 09:41:03
除了Vladirmir答案之外,你只需要处理连接条件和过滤条件。
但是,如果值在行中重复,则需要注意join的乘数效应。如果只想检查是否存在,可以使用EXISTS
SELECT oL.SERIAL_NUMBER
FROM ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS oL
WHERE
NOT EXISTS(
SELECT * FROM ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS rL
WHERE oL.SERIAL_NUMBER = rL.SERIAL_NUMBER)
AND oL.SO_LINE_NUMBER = @val发布于 2017-01-20 09:35:27
您的查询看起来几乎是正确的。
您应该在oL.SERIAL_NUMBER = rL.SERIAL_NUMBER (而不是!=)上连接,然后只留下那些具有rL.SERIAL_NUMBER IS NULL的行。此筛选器表示rL中没有包含此类SERIAL_NUMBER的行。
DECLARE @val int;
SET @val = 43770;
SELECT
oL.SERIAL_NUMBER
FROM
ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL
LEFT JOIN ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL
ON oL.SERIAL_NUMBER = rL.SERIAL_NUMBER
WHERE
oL.SO_LINE_NUMBER = @val
AND rL.SERIAL_NUMBER IS NULL
;发布于 2017-01-20 09:42:59
您可以编写如下所示的子查询:
DECLARE @val int;
SET @val = 43770;
SELECT
oL.SERIAL_NUMBER
FROM
ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL
WHERE
oL.SO_LINE_NUMBER = @val
AND oL.SERIAL_NUMBER NOT IN (
SELECT rL.SERIAL_NUMBER
FROM ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL
)
;https://stackoverflow.com/questions/41754641
复制相似问题