給定一個表產品
| PID | 姓名 |
|---|---|
| 123 | 牛奶 |
| 456 | 茶 |
| 789 | 蛋糕 |
| ... | ... |
和表銷售
| 郵票 | PID | 單位 |
|---|---|---|
| 14:54 | 123 | 3 |
| 15:02 | 123 | 9 |
| 15:09 | 456 | 1 |
| 15:14 | 456 | 1 |
| 15:39 | 456 | 2 |
| 15:48 | 789 | 12 |
| ... | ... | ... |
我如何才能獲得銷量最多的產品？我的目標是運行一個SELECT陳述句，在這個例子中，
| PID | 姓名 |
|---|---|
| 123 | 牛奶 |
| 789 | 蛋糕 |
因為這兩種產品的銷售單位總和為 12，即最大值（Tea 大于 4，盡管 Tea 的銷售額更高）。
我有以下查詢：
SELECT DISTINCT products.pid, products.name
FROM sales
INNER JOIN products ON sale.pid = products.pid
INNER JOIN (
SELECT pid, SUM(units) as sum_units
FROM sales
GROUP BY pid
) AS total_units ON total_units.pid = sales.pid
WHERE total_units.sum_units IN (
SELECT MAX(sum_units) as max_units
FROM (
SELECT pid, SUM(units) as sum_units
FROM sales
GROUP BY pid
) AS total_units
);
然而，這似乎很長，令人困惑，效率低下，甚至重復子查詢來獲取total_units，所以我想知道是否有更好的方法來完成這個。
我怎樣才能簡化這個？ORDER BY SUM(units) LIMIT 1請注意，如果有多個（即>1）產品的銷售量最多，我將無法使用。
先感謝您。
uj5u.com熱心網友回復：
自 Postgres 13 以來，它支持ties，因此您的查詢可以簡單地是這樣的：
select p.pId, p.name
from sales s
join products p on p.pid = s.pid
group by p.pId, p.name
order by Sum(units) desc
fetch first 1 rows with ties;
見演示小提琴
uj5u.com熱心網友回復：
解決您的問題：
WITH cte1 AS
(
SELECT s.pid, p.name,
SUM(units) as total_units
FROM sales s
INNER JOIN products p
ON s.pid = p.pid
GROUP BY s.pid, p.name
),
cte2 AS
(
SELECT *,
DENSE_RANK() OVER(ORDER BY total_units DESC) as rn
FROM cte1
)
SELECT pid,name
FROM cte2
WHERE rn = 1
ORDER BY pid;
作業示例：db_fiddle 鏈接
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/486194.html
標籤：sql PostgreSQL
上一篇：MySQL從查詢結果中添加列