SQL Server - 不在

发布于 2024-11-02 11:33:59 字数 310 浏览 3 评论 0原文

我需要构建一个查询，根据品牌-型号-序列号组合显示表 1 中但不在表 2 中的记录。

我知道事实上有 4 条记录不同，但我的查询总是返回空白。

SELECT  *  
FROM Table1 WHERE MAKE+MODEL+[Serial Number] NOT IN
(SELECT make+model+[serial number] FROM Table2)

表1有5条记录。

当我将查询更改为 IN 时，我得到 1 条记录。我做错了什么NOT？

原文

I need to build a query that will show me records that are in Table 1, but that are not in Table 2, based on the make-model-serial number combination.

I know for fact that there are 4 records that differ, but my query always comes back blank.

SELECT  *  
FROM Table1 WHERE MAKE+MODEL+[Serial Number] NOT IN
(SELECT make+model+[serial number] FROM Table2)

Table 1 has 5 records.

When I change the query to IN, I get 1 record. What am I doing wrong with the NOT?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谎言 2024-11-09 11:33:59

这是因为 NOT IN 的工作方式。

为了避免这些令人头疼的问题（并且在许多情况下为了更快的查询），我总是更喜欢 NOT EXISTS：

SELECT  *  
FROM Table1 t1 
WHERE NOT EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t1.MAKE = t2.MAKE
    AND   t1.MODEL = t2.MODEL
    AND   t1.[Serial Number] = t2.[serial number]);

It's because of the way NOT IN works.

To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS:

SELECT  *  
FROM Table1 t1 
WHERE NOT EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t1.MAKE = t2.MAKE
    AND   t1.MODEL = t2.MODEL
    AND   t1.[Serial Number] = t2.[serial number]);

回复收藏 0 原文

近箐 2024-11-09 11:33:59

您可能最好单独比较字段，而不是连接字符串。

SELECT t1.*
    FROM Table1 t1
        LEFT JOIN Table2 t2
            ON t1.MAKE = t2.MAKE
                AND t1.MODEL = t2.MODEL
                AND t1.[serial number] = t2.[serial number]
    WHERE t2.MAKE IS NULL

You're probably better off comparing the fields individually, rather than concatenating the strings.

SELECT t1.*
    FROM Table1 t1
        LEFT JOIN Table2 t2
            ON t1.MAKE = t2.MAKE
                AND t1.MODEL = t2.MODEL
                AND t1.[serial number] = t2.[serial number]
    WHERE t2.MAKE IS NULL

回复收藏 0 原文

记忆消瘦 2024-11-09 11:33:59

SELECT  *  FROM Table1 
WHERE MAKE+MODEL+[Serial Number]  not in
    (select make+model+[serial number] from Table2 
     WHERE make+model+[serial number] IS NOT NULL)

这对我有用，其中 make+model+[serial number] 是一个字段名称

SELECT  *  FROM Table1 
WHERE MAKE+MODEL+[Serial Number]  not in
    (select make+model+[serial number] from Table2 
     WHERE make+model+[serial number] IS NOT NULL)

That worked for me, where make+model+[serial number] was one field name

回复收藏 0 原文

残疾 2024-11-09 11:33:59

使用 LEFT JOIN 检查右侧是否有空值。

SELECT a.Id
FROM TableA a
LEFT JOIN TableB on a.Id = b.Id
WHERE b.Id IS NULL

上面的代码将根据 TableA 和 TableB 中的 Id 列来匹配 TableA 和 TableB，然后给出 B 侧为空的行。

Use a LEFT JOIN checking the right side for nulls.

SELECT a.Id
FROM TableA a
LEFT JOIN TableB on a.Id = b.Id
WHERE b.Id IS NULL

The above would match up TableA and TableB based on the Id column in each, and then give you the rows where the B side is empty.

回复收藏 0 原文

荒岛晴空 2024-11-09 11:33:59

SELECT [T1].*
FROM [Table1] AS [T1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [Table2] AS [T2]
    WHERE ([T2].[MAKE] = [T1].[MAKE]) AND
        ([T2].[MODEL] = [T1].[MODEL]) AND
        ([T2].[Serial Number] = [T1].[Serial Number])
);

SELECT [T1].*
FROM [Table1] AS [T1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [Table2] AS [T2]
    WHERE ([T2].[MAKE] = [T1].[MAKE]) AND
        ([T2].[MODEL] = [T1].[MODEL]) AND
        ([T2].[Serial Number] = [T1].[Serial Number])
);

回复收藏 0 原文