mysql - SQL 仅使用子查询列出唯一值

Question

首先让我说是的，这是一道家庭作业题。

这看起来很简单，但我无法让它工作。我刚刚开始子查询，我猜这就是我的老师想要的。

问题来了

5.  Write a SELECT statement that returns the name and discount percent 
    of each product that has a unique discount percent. In other words,
    don’t include products that have the same discount percent as another
    product.
     -Sort the results by the ProductName column.

这是我尝试过的

SELECT DISTINCT p1.ProductName, p1.DiscountPercent
FROM Products AS p1
WHERE NOT EXISTS
    (SELECT p2.ProductName, p2.DiscountPercent
     FROM Products AS p2
     WHERE p1.DiscountPercent <> p2.DiscountPercent)
ORDER BY ProductName;

非常感谢任何帮助 - 谢谢!

Answer 1

如果使用 COUNT() 检查唯一性使其变得简单，您可以在 HAVING 子句中使用它或直接选择它。

SELECT a.ProductName, a.DiscountPercent
FROM Products a
JOIN (SELECT DiscountPercent, COUNT(DiscountPercent) AS CT
      FROM Products
      GROUP BY DiscountPercent
     )b
 ON a.DiscountPercent = b.DiscountPercent
WHERE b.CT = 1

或者:

SELECT a.ProductName, a.DiscountPercent
FROM Products a
JOIN (SELECT DiscountPercent
      FROM Products
      GROUP BY DiscountPercent
      HAVING COUNT(DiscountPercent) = 1
     )b
 ON a.DiscountPercent = b.DiscountPercent