這是我目前的代碼。它是在T-SQL中。
但是，它只選擇了每一年的最大值的專案，而不是總分。
WITH cte as
(
SELECT
CONCAT(forename,' ',surname) AS driver_name,
年。
(CONVERT(FLOAT, points)) AS race_points,
row_number() over (partition by year) keyword">year order by (CONVERT(FLOAT, points)) desc) as rn
FROM[/span
結果 ASr
INNER JOIN建構式 AS c ON r.constructorId = c.constructorId
INNER JOIN drivers AS d ON r.driverId = d.driverId
INNER JOIN races AS rc ON r.raceId = rc.raceId
)
SELECT * FROM cte
WHERE rn = 1
這就是sub的模樣
。| driver_name | 年race_points |
|---|---|
| a | 2011 |
我想讓它看起來像這樣
我想讓它看起來像這樣。
| driver_name | 年 | max_points |
|---|---|---|
| b | 2011 |
我目前得到的是這樣的結果
如果有任何幫助，我將非常感激。
uj5u.com熱心網友回復：
你想用視窗函式聚合：
WITH cte as (
SELECT CONCAT(forename, ' ', surname) AS driver_name,
年, SUM( points) as race_points,
RANK() OVER (PARTITION BY year) keyword">year ORDER BY SUM(point) DESC) as seqnum
FROM結果r JOIN
建構式 c
ON r.constructorId = c.constructorId JOIN
驅動程式d
ON r.driverId = d.driverId JOIN
賽事 rc
ON r.raceId = rc.raceId
GROUP BY CONCAT(forename, ' ', surname), year)
)
SELECT *
FROM cte
WHERE seqnum = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/332043.html
標籤：
上一篇：有沒有可能做一個有條件的連接？