我正在嘗試構建一個 SQL 查詢，該查詢比我習慣撰寫的要復雜一些。
示例資料：
event_id event_source duration name
67538576 9071 67 [Lpr] abc
67371890 9071 62 [Lpr] Test data
67338632 9071 63 [Lpr] Test data
67331147 9071 63 [Lpr] Test data
67325698 9071 235 [Lpr] Test data
63098403 9071 2733441 [Lpr] Test data diff
63023403 8001 2241 [Cam] Some name
63013203 8001 212 [Cam] Some name
62013203 7000 60 [Lpr] Name B
62012203 7000 70 [Lpr] Name B
到目前為止，我有一個作業查詢
SELECT min(name) as name ,sum(duration) as TotalDuration
FROM table
WHERE duration > 30 AND name LIKE '[[]Lpr]%'
GROUP BY event_source
ORDER BY TotalDuration Desc
目前它將回傳
Name TotalDuration
[Lpr] Test data 2733931
[Lpr] Name B 130
但我希望該查詢始終回傳具有最大 event_id 的名稱，因此好的結果將是“[Lpr] abc”和“[Lpr] Name B”。
uj5u.com熱心網友回復：
我想是這樣
SELECT T1.name,
T2.TotalDuration
FROM table T1 INNER JOIN
( SELECT max(event_id) as event_id,sum(duration) as TotalDuration
FROM table
WHERE duration > 30 AND name LIKE '[[]Lpr]%'
GROUP BY event_source ) T2
ON T1.event_id = T2.event_id
ORDER BY T2.TotalDuration Desc
或者您可以使用視窗函式win 函式 sum
對不起錯誤:)
uj5u.com熱心網友回復：
如果我理解正確，您可以使用視窗分析函式：
select name, TotalDuration
from (
select *,
Sum(duration) over(partition by event_source) TotalDuration,
Row_Number() over(partition by event_source order by event_id desc) rn
from t
where duration > 30 and name LIKE '[[]Lpr]%'
)t
where rn = 1
order by TotalDuration desc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448979.html