基于 where 子句和句柄中的多列组合的 SQL 查询 'IN' or Not In 运算符

Question

我需要检查表中是否有 2 列组合用于表中的唯一列 ID。示例：我有一个表 表 A

----------------------------------
ID      TYPE          VALUE
---------------------------------
1   CUST        1
1   SOLDTO      3
1   SHIPTO      2
2   CUST        1
2   SOLDTO      2
2   SHIPTO      5
3   CUST        1
3   SOLDTO      2
3   SHIPTO      3
----------------------------------

我需要检查条件组合

if ( TYPE = 'CUST' VALUE = 1 ) 
and ( TYPE = 'SOLDTO' VALUE  IN RANGE 2 to 3 ) 
and (TYPE = 'SHIPTO' VALUE NOT EQ 3)

应返回条目

2   CUST        1
2   SOLDTO      2
2   SHIPTO      5

请协助。

Answer 1

您可以使用存在和不存在来尝试

select * from t where exists
( select 1 from t t1 where t1.ID=t.ID 
and t1.TYPE in('CUST','SOLDTO') 
and t1.VALUE>=1 and t1.VALUE<=3)
and not exists ( select 1 from t t2 where t2.ID=t.ID
and t2.TYPE='SHIPTO' and t2.VALUE!=3)

Answer 2

找到有趣的 id：

SELECT ID FROM table 
GROUP BY id
HAVING SUM(
case when (type = 'CUST' and VALUE = 1) or
(TYPE = 'SOLDTO' and VALUE between 2 and 3) or
(TYPE = 'SHIPTO' and VALUE <> 3)
THEN 1 else 0 
end
) = 3

如果需要所有行数据，请将其联接回表：

WITH cte AS (
--put query from above inside these brackets
)
SELECT t.* FROM t INNER JOIN cte.id = t.Id

Answer 3

你可以试试这个。

DECLARE @TableA TABLE (ID  INT, [TYPE] VARCHAR(20), VALUE INT)
INSERT INTO @TableA VALUES
(1 ,'CUST  ', 1),
(1 ,'SOLDTO', 3),
(1 ,'SHIPTO', 2),
(2 ,'CUST  ', 1),
(2 ,'SOLDTO', 2),
(2 ,'SHIPTO', 5),
(3 ,'CUST  ', 1),
(3 ,'SOLDTO', 2),
(3 ,'SHIPTO', 3)
SELECT * FROM @TableA TA
WHERE 
EXISTS(
SELECT ID FROM @TableA T
WHERE T.ID = TA.ID
GROUP BY ID
HAVING 
COUNT(CASE WHEN T.[TYPE]='CUST' AND T.VALUE = 1 THEN 1 END) = 1 
AND   COUNT(CASE WHEN T.[TYPE]='SOLDTO' AND T.VALUE >=2 AND T.VALUE < 3 THEN 1 END) = 1 
AND   COUNT(CASE WHEN T.[TYPE]='SHIPTO' AND T.VALUE <> 3 THEN 1 END) = 1 
)

结果：

ID          TYPE                 VALUE
----------- -------------------- -----------
2           CUST                 1
2           SOLDTO               2
2           SHIPTO               5