SQL-至少排除一次满足要求的列的值

问题描述

有两个表Client和Stock:

Table Client
    Column IDC       (primary key, int, not null)

Table Stock
    Column IDS       (primary key, int, not null)
    Column IDC       (int, not null)
    Column Type      (bit, not null)
    Column Price     (decimal(10,2), null)

应将它们视为由Client.IDC = Stock.IDC连接.

使用Type = 1和Price not NULL获取客户端的IDC是微不足道的.但是，获取其余IDC的列表对我来说并不简单.以下尝试未提供正确的IDC列表:

Getting IDC of clients with Type = 1 and Price not NULL is trivial. However, it's not trivial to me to get the list of the remaining IDC. The following attempt does not deliver correct list of IDC:

SELECT [Client].[IDC]
FROM [Client] LFEFT JOIN [Stock] on [Client].[IDC] = [Stock].[IDC]
WHERE NOT([Stock].[Type] = 1 AND [Stock].[Price] IS NOT NULL)

此语句返回某些IDC，因为对于它们来说，实际上是带有Type = 0的行，还有带有Type = 1和Price not NULL的其他行.我应该如何继续获取完全不包含Type = 1和Price not NULL的行的IDC?

This statement returns some IDC which should be excluded as there are, for them, indeed rows with Type = 0 but also other rows with Type = 1 and Price not NULL. How should I proceed to get IDC which have no row with Type = 1 and Price not NULL at all?

推荐答案

在ON子句中移动条件.

这意味着ON子句中的条件将在与Client联接之前过滤表Stock中的行.

This means that the condition in the ON clause will filter the rows in table Stock before joining with Client.

SELECT [Client].[IDC]
FROM [Client] 
     LEFT JOIN [Stock] 
         on [Client].[IDC] = [Stock].[IDC]
              AND [Stock].[Type] = 1 
              AND [Stock].[Price] IS NOT NULL
WHERE [Stock].[IDC] IS NULL

这也可以使用NOT EXISTS

SELECT [Client].[IDC]
FROM [Client] c
WHERE NOT EXISTS
(
    SELECT 1
    FROM [Stock] s
    WHERE c.[IDC] = s.IDC
              AND s.[Type] = 1 
              AND s.[Price] IS NOT NULL
)

这篇关于SQL-至少排除一次满足要求的列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！