我有一個參與者表：
我寫了一個 CTE，它給出了下表：
CTE 代碼：
with cte as
(
select t.per1,t.per2,t.met1,concat(t.per1,t.per2) concated from
(
select p1.person_id per1, p1.meeting_id met1, p2.person_id per2 from participant p1 cross join participant p2
where p1.meeting_id=p2.meeting_id and p1.person_id<>p2.person_id
)t
where t.per1<t.per2
--order by t.per1,t.per2
)
現在我想從這個 cte 中獲取連接數最大的行。即我想要行
Per1 Per2 Met1 Concated
1 2 10 12
1 2 20 12
1 2 30 12
因為 concated=12出現最大次數
我如何達到同樣的效果？我嘗試使用 count 和 group by 但無法同時回傳 Per1、Per2 和 Met1。
提前致謝。
uj5u.com熱心網友回復：
我們可以COUNT()在最里面的查詢中使用分析來查找會議與會者對的數量：
WITH cte AS (
SELECT p1.person_id per1, p1.meeting_id met, p2.person_id per2,
COUNT(*) OVER (PARTITION BY p1.person_id, p2.person_id) cnt
FROM participant p1
INNER JOIN participant p2
ON p1.meeting_id = p2.meeting_id AND p1.person_id < p2.person_id
)
SELECT TOP 1 WITH TIES met, per1, per2
FROM cte
ORDER BY RANK() OVER (ORDER BY cnt DESC);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457443.html
下一篇：如何查找日期名稱？