我有一張這樣的表（來自 LeetCode）：
| requester_id | 接受者_id | 接受日期 |
|---|---|---|
| 1 | 2 | 2016/06/03 |
| 1 | 3 | 2016/06/08 |
| 2 | 3 | 2016/06/08 |
| 3 | 4 | 2016/06/09 |
找到擁有最多朋友和最多朋友號碼的人。
預期輸出：
| ID | 數 |
|---|---|
| 3 | 3 |
我為答案撰寫了這個查詢并且它有效：
select a.f as "id", count(a.f) as "num"
from (
select requester_id as f
from requestaccepted
union all
select accepter_id as f
from requestaccepted ) a
group by a.f
order by count(a.f) desc
limit 1;
我不是 100% 相信使用limit 1是最好的解決方案。
什么應該是替代/更好的選擇？
uj5u.com熱心網友回復：
LIMIT 1afterORDER BY就可以得到一個獲勝者：
SELECT a.id, count(*) AS num
FROM (
SELECT requester_id AS id FROM requestaccepted
UNION ALL
SELECT accepter_id AS id FROM requestaccepted
) a
GROUP BY a.id
ORDER BY num DESC
LIMIT 1;
如果有多個獲勝者，您將獲得任意選擇。您可以添加其他ORDER BY運算式以獲得確定性選擇。
如果你必須避免LIMIT/ FETCH FIRST（真的嗎？）視窗函式row_number()是一個（更昂貴的！）替代方案：
SELECT id, num
FROM (
SELECT a.id, count(*) AS num
, row_number() OVER (ORDER BY count(*) DESC) AS rn
FROM (
SELECT requester_id AS id FROM requestaccepted
UNION ALL
SELECT accepter_id AS id FROM requestaccepted
) a
GROUP BY a.id
) sub
WHERE rn = 1;
要獲得所有與勝利相同的 ID，只需添加WITH TIES. 必須使用標準 SQL 語法FETCH FIRST 1 ROWS而不是 Postgres 快捷方式LIMIT 1來添加子句。
SELECT a.id, count(*) AS num
FROM (
SELECT requester_id AS id FROM requestaccepted
UNION ALL
SELECT accepter_id AS id FROM requestaccepted
) a
GROUP BY a.id
ORDER BY count(*) DESC
FETCH FIRST 1 ROWS WITH TIES;
沒有其他ORDER BY運算式，這將解決關系。
如果你必須避免LIMIT/ FETCH FIRST（真的嗎？）視窗函式rank()是一個（更昂貴的！）替代方案：
SELECT id, num
FROM (
SELECT a.id, count(*) AS num
, rank() OVER (ORDER BY count(*) DESC) AS rnk
FROM (
SELECT requester_id AS id FROM requestaccepted
UNION ALL
SELECT accepter_id AS id FROM requestaccepted
) a
GROUP BY a.id
) sub
WHERE rnk = 1
ORDER BY id; -- optional
db<>fiddle here - 帶有擴展測驗用例以顯示平局
看：
- 大于或等于 ALL() 且等于 MAX() 速度
- TOP n WITH TIES 的 PostgreSQL 等效項：限制“與關系”？
uj5u.com熱心網友回復：
由于您只需要數量最多的朋友，因此您可以直接獲取已接受 id 的數量，并且最多可以使用。
select id ,max(num) from
(
select accepter_id id ,count(*) num from requestaccepted
group by accepter_id
) a
uj5u.com熱心網友回復：
另一種有趣的方式可能是使用Join LateralwithLimit
模式和插入陳述句：
create table requestaccepted(requester_id int, accepter_id int, accept_date date);
insert into requestaccepted values(1, 2, '2016/06/03');
insert into requestaccepted values(1, 3, '2016/06/08');
insert into requestaccepted values(2, 3, '2016/06/08');
insert into requestaccepted values(3, 4, '2016/06/09');
輸出：
SELECT id,count(*)num
FROM requestaccepted r
JOIN LATERAL (VALUES(r.requester_id),(r.accepter_id)) s(id) ON TRUE
group by id
order by num desc
Limit 1
輸出：
| ID | 數 |
|---|---|
| 3 | 3 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439758.html
標籤：sql PostgreSQL sql 限制