我當前的 T-SQL 查詢提供以下結果：
詢問：
WITH CTE AS
(
SELECT SubscriberID, sum(valueMB) as ValuesMB
FROM dbo.InternetNetwork
GROUP BY SubscriberID
),
CTE2 AS (
SELECT ab.planID, a.SubscriberID, MAX(ValuesMB) as MaximumValue
FROM CTE AS a
left join
Subscriber as ab on a.SubscriberID= ab.SubscriberID
GROUP BY ab.planID, a.SubscriberID
)
select *
FROM CTE2 as b
ORDER BY b.MaximumValue desc
輸出：
planID | SubscriberID | MaxValue
19 1555 97536.00
18 3528 97478.00
2 4029 93413.00
查詢#2：
WITH CTE AS
(
SELECT SubscriberID, sum(valueMB) as ValuesMB
FROM dbo.InternetNetwork
GROUP BY SubscriberID
),
CTE2 AS(
SELECT ab.planID, MAX(ValuesMB) as MaximumValue
FROM CTE AS a
left join
Subscriber as ab on a.SubscriberID= ab.SubscriberID
GROUP BY ab.planID
)
SELECT pl.OperatorID, MAX(b.MaximumValue) as Super
FROM CTE2 as b
left join
Plan as pl on b.planID= pl.planID
GROUP BY pl.operatorID
ORDER BY pl.operatorID
輸出#2：
OperatorID | Value
1 93413.00
2 86017.00
3 97536.00
我還想包含一個subscriberID，但我無法找到一種方法，因為唯一的方法是包含在最后一個 SELECT 中并添加到 GROUP BY，完成后會弄得一團糟不準確的結果。
我想要的輸出：
OperatorID | Value | SubscriberID
1 93413.00 4029
2 86017.00 164
3 97536.00 1544
互聯網網路資料：
SubscriberID ValuesMB
1 28
1 27
2 27
2 27
2 27
3 29
3 28
3 27
3 27
4 27
4 27
4 29
訂戶資料：
SubscriberID PersonID PlanID
1 1 3
2 2 10
3 2 6
4 3 14
5 3 1
6 4 18
7 5 5
8 5 1
9 5 9
10 5 16
11 6 13
12 6 13
13 6 20
14 6 16
15 7 4
計劃資料
PlanID OperatorID
1 1
2 1
3 2
4 2
5 2
6 2
7 2
8 2
9 2
10 2
11 2
12 3
13 3
14 3
15 3
16 3
17 3
18 3
19 3
20 3
這些表有點像這個相關的 InternetNetwork-> Subscriber -> Plan。InternetNetwork 包含每個訂閱者使用了多少。每個訂戶都有與其相關聯的計劃。每個計劃包含一個不同的操作員，只有三個。我想列出所有三個運營商，由具有運營商和訂戶 ID 的計劃的訂戶傳輸的資料。
uj5u.com熱心網友回復：
視窗函式允許您在選擇中包含欄位以及聚合函式。你可以做這樣的事情
;WITH CTE AS
(
SELECT I.SubscriberID,
S.PlanID,
SUM(ValuesMB) OVER(PARTITION BY i.SubscriberID)as ValuesMB
FROM dbo.InternetNetwork I
JOIN Subscriber S
ON I.SubscriberID = S.SubscriberID
),
CTE2 AS
(
SELECT p.operatorID,
a.SubscriberID,
a.ValuesMB,
ROW_NUMBER() OVER(PARTITION BY p.operatorID ORDER BY a.ValuesMB DESC) as rn
FROM CTE a
join [Plan] P
on a.planID = P.planID
)
SELECT operatorID,
ValuesMB,
SubscriberID
FROM CTE2
where rn = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/391563.html
標籤：sql sql-server 数据库 查询语句
上一篇：查詢在1個主列上呼叫2個外鍵