我使用了一個具體但假設的例子。
考慮一個包含射擊比賽結果的資料庫，其中每個參賽者進行了幾個系列的射擊。DB 包含 3 個表：Competitors、Series和Shots。
競爭對手：
| ID | 姓名 |
|---|---|
| 1 | 一種 |
| 2 | 乙 |
系列：
| ID | 競爭對手 ID |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
鏡頭：
| ID | 序列號 | 分數 |
|---|---|---|
| 1 | 1 | 8 |
| 2 | 1 | 8 |
| 3 | 1 | 8 |
| 4 | 2 | 10 |
| 5 | 2 | 7 |
| 6 | 2 | 6 |
| 7 | 3 | 10 |
| 8 | 3 | 8 |
| 9 | 3 | 6 |
| 10 | 4 | 8 |
| 11 | 4 | 8 |
| 12 | 4 | 7 |
| 13 | 5 | 7 |
| 14 | 5 | 10 |
| 15 | 5 | 7 |
| 16 | 6 | 7 |
| 17 | 6 | 10 |
| 18 | 6 | 7 |
（帶有上述陳述句的 DDL：dbfiddle）
我需要的是通過多種條件對競爭對手進行排序，這些條件是：
- 全系列總分
- 中擊數（中擊得10分）
- 下一步訂購是：
- 上賽季最高分
- 僅次于上一個系列的最高分
- 僅次于上一個系列的最高分
- ...
以此類推，以獲取比賽中的系列數量。
The query that uses the first two order conditions is quite straightforward:
SELECT comp.name,
SUM(shots.score) AS score,
SUM(IIF(shots.score = 10, 1, 0)) AS centerHits
FROM Shots shots
INNER JOIN Series series ON series.id = shots.serieId
INNER JOIN Competitors comp ON comp.id = series.competitorId
GROUP BY comp.name
ORDER BY score DESC, centerHits DESC
It produces following results:
| name | score | centerHits |
|---|---|---|
| A | 71 | 2 |
| B | 71 | 2 |
With the 3rd order condition I expect B competitor to be above A, because both have the same total score, the same centerHits and the same score for the last serie (24), but the score of next to last serie of B is 24 while A's score is only 23.
I wonder if it's possible to make a query that uses the third and following order conditions.
uj5u.com熱心網友回復：
您應該能夠非常簡單地做到這一點，因為您的要求可以通過正常的聚合和視窗函式來完成。
對于每個排序級別：
- “所有系列的總分”可以通過將所有分數相加來滿足。
- “中點數（中點得分為10分）”可以滿足條件計數。
- 要按日期向后排序每個系列，我們可以使用 聚合每個系列的總分（我們使用視窗函式計算）
STRING_AGG，按日期（或 id）排序聚合。然后，如果我們按該聚合對最終查詢進行排序，則后面的系列將首先排序。與其他答案相反，此方法允許您按任意數量的系列進行排序。
由于沒有日期列，目前尚不清楚如何定義“以后”和“較早”，但我已將其用作series.id代理。
SELECT
comp.name,
SUM(shots.score) as totalScore,
COUNT(CASE WHEN shots.score = 10 THEN 1 END) AS centerHits,
STRING_AGG(NCHAR(shots.MaxScore 65), ',') WITHIN GROUP (ORDER BY series.id DESC) as AllShots
FROM (
SELECT *,
SUM(shots.score) OVER (PARTITION BY shots.serieID) MaxScore
FROM Shots shots
) shots
INNER JOIN Series series ON series.id = shots.serieId
INNER JOIN Competitors comp ON comp.id = series.competitorId
GROUP BY
comp.id,
comp.name
ORDER BY
totalScore DESC,
centerHits DESC,
AllShots DESC;
請注意，按名稱分組時，您還應該添加主鍵，GROUP BY因為名稱可能不是唯一的。
shots一個類似但稍微復雜一點的查詢是在派生表中進行預聚合。這可能比使用視窗函式執行得更好。
SELECT
comp.name,
SUM(shots.totalScore) as totalScore,
SUM(centerHits) AS centerHits,
STRING_AGG(NCHAR(shots.totalScore 65), ',') WITHIN GROUP (ORDER BY series.id DESC) as AllShots
FROM (
SELECT
shots.serieId,
SUM(shots.score) as totalScore,
COUNT(CASE WHEN shots.score = 10 THEN 1 END) AS centerHits
FROM Shots shots
GROUP BY
shots.serieId
) shots
INNER JOIN Series series ON series.id = shots.serieId
INNER JOIN Competitors comp ON comp.id = series.competitorId
GROUP BY
comp.id,
comp.name
ORDER BY
totalScore DESC,
centerHits DESC,
AllShots DESC;
db<>小提琴
uj5u.com熱心網友回復：
看來您需要一個多級查詢，每個查詢都建立在先前的一個之上。
具有別名 PQ 的 INNER-MOST 查詢是對每個 SerieID 的簡單求和，它獲取每個相應集合的總中心命中數和總點數。類似于您的計數。
從此，您需要知道哪個系列是最新的（最新的），并以您的方式回溯到之前的系列。通過使用 OVER / PARTITION，我正在加入系串列以獲取競爭對手的 ID 和名稱。
通過根據每個競爭對手對資料進行磁區，并根據 SerieID DESCENDING 應用順序，我得到的行號會將最新的 row_number() 分別變為 1、2 和 3，這樣對于競爭對手 A，誰有 SerieID 1，然后是 2，然后 3 將具有最終的“MostRecent”列分別為 3、2 和 1，因此 SerieID 3 = 1 - 最新的，而 SerieID 1 = 3 OLDEST serie 或競爭對手。
同樣對于第二個競爭對手 B，SerieID 4、5 和 6 分別變為 3、2、1。所以現在，您有基礎知道什么是最新的（1 = 最近的）、倒數第二個（2 = 下一個最近的）和倒數第二個（3...）
現在這兩個部分都設定好了，我可以總結各自的總數，中心命中，現在明確地知道最近的 (1) 是什么，第二個最新的 (2) 和倒數第三個 (3) 是。這些被添加到組中。
現在，如果一個參賽者有 6 個射擊系列，而另一個有 4 個系列（并不是說它會在真正的比賽中發生，而是要了解背景關系），則 6 系列將其 LATEST 作為 MostRecent = 1，與 4 系列類似，第 4 個系列將是 MostRecent = 1。
因此，在 COMPETITOR 級別的最終分組中，您可以評估所有有問題的部分。
select
F.Name,
F.CompetitorID,
sum( F.SeriesTotalScore ) TotalScore,
sum( F.CenterHits ) CenterHits,
sum( case when F.MostRecent = 1
then F.SeriesTotalScore else 0 end ) MostRecentScore,
sum( case when F.MostRecent = 2
then F.SeriesTotalScore else 0 end ) SecondToMostRecentScore,
sum( case when F.MostRecent = 3
then F.SeriesTotalScore else 0 end ) ThirdToMostRecentScore
from
( select
c.Name,
Se.CompetitorID,
PQ.SerieId,
PQ.CenterHits,
PQ.SeriesTotalScore,
ROW_NUMBER() OVER( PARTITION BY Se.CompetitorID
order by PQ.SerieId DESC) AS MostRecent
from
( select
s.serieId,
sum( case when s.score = 10 then 1 else 0 end ) as CenterHits,
sum( s.Score ) SeriesTotalScore
from
Shots s
group by
s.SerieID ) PQ
Join Series Se
on PQ.SerieID = se.id
JOIN Competitors c
on Se.CompetitorID = c.id
) F
group by
F.Name,
F.CompetitorID
order by
sum( F.SeriesTotalScore ) desc,
sum( F.CenterHits ),
sum( case when F.MostRecent = 1
then F.SeriesTotalScore else 0 end ) desc,
sum( case when F.MostRecent = 2
then F.SeriesTotalScore else 0 end ) desc,
sum( case when F.MostRecent = 3
then F.SeriesTotalScore else 0 end ) desc
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/452428.html
標籤：sql sql-server sql-server-2017