查詢沒有兩個相等的“product_name”的“Order_num”

我有一個具有以下結構的表：

create table A_TEST
(
 order_num      int,
 product_name   varchar(50),
 product_amount int,
 product_price  int
);

樣本數據 ：

insert into a_test (order_num , product_name , product_amount , product_price)
values (1,'APPLE',1000,2000),
      (1,'APPLE',2000,5000),
      (1,'APPLE',4000,3000),
      (2,'ORANGE',280,3000),
      (2,'APPLE',200,3000),
      (3,'BANANA',2000,3000),
      (3,'APPLE',200,3000),
      (4,'POTATO',123,3000),
      (4,'POTATO',2000,3000),
      (4,'APPLE',2000,3000),
      (5,'APPLE',360,3000),
      (5,'POTATO',2000,3000),
      (5,'TOMATO',360,3000),
      (5,'BANANA',123,3000),
      (5,'ORANGE',126,3000);

我想要的是找到那些order_num具有不同product_names 的 s 以便一個 order_num 的兩個 product_name 不應該是相同的。期望的結果是(2,3,5). 我試圖解決這個問題，Exists但它不會返回正確的結果。

SELECT *
FROM A_TEST A
WHERE EXISTS (SELECT *
             FROM A_TEST B
             WHERE A.order_num = B.order_num
             AND A.product_name <> B.product_name);

我知道為什麼這不會返回正確的結果。原因是如果它找到至少一個與另一個不同的product_name，它將返回它的order_num。我想知道您是否可以提出更好的解決方案。

提前致謝

經典解決方案：

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1;

定制解決方案：

SELECT DISTINCT t1.order_num
FROM a_test t1
JOIN a_test t2 ON t1.order_num = t2.order_num
WHERE t1.product_name != t2.product_name;

WITH cte AS ( SELECT order_num, 
                    DENSE_RANK() OVER (PARTITION BY order_num ORDER BY product_name) dr
             FROM a_test )
SELECT DISTINCT order_num
FROM cte
WHERE dr = 2;

…

小提琴

---

您使用的那個 ‘Join’ 再次沒有返回所需的結果。它返回那些具有相同 order_num 但它們的 product_name 不同的記錄，並且不返回具有相同 order_num 且它們的 product_name 相同的記錄。就是這個——Pantea

您的意思是您只需要 (1) 至少有 2 個產品和 (2) 兩次沒有產品的訂單嗎？如果是這樣，則添加.. AND COUNT(DISTINCT product_name) = COUNT(product_name)到第一個查詢。

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1
AND COUNT(DISTINCT product_name) = COUNT(product_name);

小提琴

引用自：https://dba.stackexchange.com/questions/279885