如果特定值的某些条件返回FALSE，则返回另一个值。

Question

有两个表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 Issued    (bit, not null)
    Column Price     (decimal(10,2), null)

它们将被认为是由Client.IDC = Stock.IDC连接的。

讨论here展示了如何将IDC分成以下两组：

1. IDC与Type = 1和Price not NULL，以及
2. 剩余IDC，即与Type = 1和Price not NULL完全没有行的IDC

对于第二组，在IDC和Price一起返回Issued = 1的情况下，是否可行？所需的输出应该包含IDC，其中不存在带有Type = 1和Price not NULL的行，但是有Issued = 1和Price not NULL的行是可用的。

示例：

Table Client
    IDC
    1
    2
    3
    4
    5
    6
    7
    8
    9

Table Stock
    IDS  IDC  Type  Issued  Price

    1    1    1     0       20
    2    1    0     1       50
    3    3    1     0       NULL
    4    3    0     1       90
    5    4    1     0       10
    6    4    0     0       70
    7    5    1     0       NULL
    8    5    0     0       30
    9    6    0     0       40
    10   6    0     1       80
    11   7    0     1       NULL
    12   8    1     1       60
    13   9    1     1       NULL

Desired return [IDC, Price]: [3,90], [6,80]

我的尝试没有成功：

SELECT C.IDC, S.Price
FROM Client C LEFT JOIN Stock S
ON C.IDC = S.IDC
    AND S.Type = 1
    AND S.Price IS NOT NULL
WHERE
    S.IDC IS NULL
    AND S.Issued = 1

我的假设是，WHERE子句中的第二个条件由于WHERE子句中的第一个条件而变得多余。不过，我真的搞不懂。我正在使用2008。

Answer 1

这就是你想要的吗？

SELECT z.IDC, z.PRICE
FROM Stock z
WHERE z.IDC IN 
(
    SELECT c.IDC IDC
    FROM [Client] c
            LEFT JOIN Stock S 
                on C.IDC = S.IDC
                    AND S.Type = 1 
                    AND S.Price IS NOT NULL
        WHERE S.IDC IS NULL
) AND Z.PRICE IS NOT NULL AND z.Issued = 1

是的，它实际上只能用Stock编写(不使用JOIN和Client)：

SELECT Z.IDC, Z.Price
FROM Stock Z
WHERE Z.IDC NOT IN
(
    SELECT S.IDC
    FROM Stock S
    WHERE S.Type = 1 AND S.Price IS NOT NULL
)
AND Z.Issued = 1
AND Z.Price IS NOT NULL

Answer 2

你可能在找这个

SELECT C.IDC, S.Price
FROM Client C 
LEFT JOIN Stock S  ON C.IDC = S.IDC
WHERE
    (S.IDC IS NULL AND S.Issued = 1) OR 
    (S.Type = 1 AND S.Price IS NOT NULL)