我有一個帶有發送者和接收者概念的交易表，可以發送交易：
現在我想獲得一個結果集，該結果集應包含每個接收者的唯一發送者數量范圍：
- 一個接收者從 1 個發送者接收
- 一個接收者接收 2-5 個發送者
- 一個接收者接收來自 6-10 個發送者的資訊
- 一個接收者接收來自 11-20 個發送者的資訊
- 一個接收者接收超過 20 個發送者
我嘗試過但無法獲得所需的結果集。任何人都可以幫助我。
uj5u.com熱心網友回復：
作為評論，這將是一團糟。您并沒有真正提供示例資料和所需的輸出（我不接受圖片作為示例）。無論如何，您所描述的是一個簡單的count()：
select receiver, count(distinct sender) senderCount
from myTable
group by receiver
order by count(distinct sender);
會做的。將結果放入像 1、2-5、... 這樣的桶可以用不同的方式完成。IE：
with counts(receiver, senderCount) as
(
select receiver, count(distinct sender)
from myTable
group by receiver
)
select receiver,
case
when senderCount = 1 then '1'
when senderCount > 1 and senderCount <= 5 then '2-5'
when senderCount > 5 and senderCount <= 10 then '6-10'
when senderCount > 10 and senderCount <= 20 then '11-20'
when senderCount > 20 then 'Over 20'
end as individualGroup
from counts;
uj5u.com熱心網友回復：
創建一個存盤范圍的（虛擬）表，然后將其與聚合資料連接：
SELECT ranges.*, COUNT(*) AS ReceiversInRange --, other aggregates
FROM (VALUES
(1, 1, 1, 'Low risk'),
(2, 2, 5, 'Low risk'),
(3, 6, 10, 'Medium risk'),
(4, 11, 20, 'Medium-high'),
(5, 20, NULL, 'High risk')
) AS ranges(id, lb, ub, label)
INNER JOIN (
SELECT COUNT(DISTINCT Sender) AS SenderCount --, other aggregates
FROM t
GROUP BY Receiver
) AS aggdata ON SenderCount >= lb AND (SenderCount <= ub OR ub IS NULL)
GROUP BY id, lb, ub, label
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/350614.html
標籤：sql sql-server 通过...分组 范围