我有以下資料：
| machine_name | 獎勵分數 | recipt_no |
|---|---|---|
| LEKKSERVER01 | 57.5 | 43543 |
| LEKKSERVER01 | 34 | 53453 |
| 愛馬仕 | 23 | 35353 |
| IKOYSERVER01 | 24 | 35636 |
| IKOYSERVER01 | 20.5 | 63653 |
當我使用這個查詢
SELECT l.machine_name,
t.points
FROM
(
SELECT machine_name,
SUM(reward_points) as points
FROM loyalty
GROUP BY machine_name
) t
JOIN loyalty l ON l.machine_name = t.machine_name
我得到以下結果
| machine_name | 積分 |
|---|---|
| LEKKSERVER01 | 91.5 |
| LEKKSERVER01 | 91.5 |
| 愛馬仕 | 23 |
| IKOYSERVER01 | 44.5 |
| IKOYSERVER01 | 44.5 |
然而，我期待的結果是這樣的。
| machine_name | 積分 |
|---|---|
| LEKKSERVER01 | 91.5 |
| 愛馬仕 | 23 |
| IKOYSERVER01 | 44.5 |
我的問題是，如何洗掉重復項并可能回傳最高點的行？
我想退回這樣的東西
| machine_name | 積分 |
|---|---|
| LEKKSERVER01 | 91.5 |
uj5u.com熱心網友回復：
洗掉JOIN并使用HAVING
SELECT machine_name,
SUM(reward_points) as points
FROM loyalty
GROUP BY machine_name
HAVING SUM(reward_points) >= all (
SELECT SUM(reward_points)
FROM loyalty
GROUP BY machine_name
)
如果有多個machine_name具有最高點的行（即它處理平局），則此解決方案可以回傳多個行。PostgreSql 13 中的等效 SQL 應該是
SELECT machine_name,
SUM(reward_points) as points
FROM loyalty
GROUP BY machine_name
ORDER BY points DESC
FETCH FIRST 1 ROWS WITH TIES
uj5u.com熱心網友回復：
使用max(t.points)連同group by machine_name：
SELECT l.machine_name,
Max(t.points) AS points
FROM (SELECT machine_name,
Sum(reward_points) AS points
FROM loyalty
GROUP BY machine_name) t
JOIN loyalty l
ON l.machine_name = t.machine_name
GROUP BY machine_name
只獲得最高的一行points：
SELECT l.machine_name,
Max(t.points) AS points
FROM (SELECT machine_name,
Sum(reward_points) AS points
FROM loyalty
GROUP BY machine_name) t
JOIN loyalty l
ON l.machine_name = t.machine_name
GROUP BY machine_name
ORDER BY points DESC
LIMIT 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/407722.html
標籤：
上一篇：按型別選擇具有最大值的值