獲得相同輸出的更好方法是什么？
我只想要那些喜歡“香蕉”而不喜歡“蘋果”的顧客
例子：
101、102和108都喜歡香蕉和蘋果，所以我不想要
105喜歡蘋果所以不想要
106不喜歡香蕉所以不想要
預期結果應為 103,104，
DECLARE @SampleTable TABLE
(
CustomerNumber INT,
Likes VARCHAR(20),
Random VARCHAR(5)
);
INSERT INTO @SampleTable (CustomerNumber, Likes, Random)
VALUES
(101, 'Banana', 'A'),
(101, 'Orange', 'A'),
(101, 'Melon', '1'),
(101, 'Apple', 's'),
(102, 'Banana', 'f'),
(102, 'Apple', 'g'),
(103, 'Banana', 'h'),
(103, 'Melon', 'j'),
(104, 'Banana', 'A'),
(104, 'Mango', '1'),
(105, 'Apple', 's'),
(106, 'Grapes', 'f'),
(107, 'Apple', 'g'),
(108, 'Banana', 'h'),
(108, 'Apple', 'j');
SELECT * FROM @SampleTable;
SELECT * FROM @SampleTable
WHERE CustomerNumber NOT IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Apple')
AND CustomerNumber IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Banana')
uj5u.com熱心網友回復：
您可以直接使用一種條件，從而洗掉子查詢之一：
SELECT *
FROM @SampleTable
WHERE Likes = 'Banana'
AND CustomerNumber NOT IN
(SELECT CustomerNumber FROM @SampleTable WHERE Likes = 'Apple')
或者對EXISTS相關子查詢執行相同操作：
SELECT *
FROM SampleTable t1
WHERE Likes = 'Banana'
AND NOT EXISTS
(SELECT 1
FROM SampleTable t2 WHERE t2.Likes = 'Apple'
AND t1.CustomerNumber=t2.CustomerNumber);
演示（基于 MySQL）
uj5u.com熱心網友回復：
另一種方式，但不一定更好
with nuts as (
select *,
Max(case when likes='apple' then 1 else 0 end) over(partition by customernumber) apple,
Max(case when likes='banana' then 1 else 0 end) over(partition by customernumber) banana
from SampleTable
)
select customernumber, likes, random
from nuts
where banana=1 and apple=0
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411240.html
標籤：
上一篇：獲取多行相當于兩個欄位上的除法
下一篇：從行內SQL連續執行存盤程序