sql - 使用 SELECT (max)T-SQL 返回额外列

Question

第一次发帖，请多多指教。 [SQL Server 2014]

我当前正在对如下所示的表格运行选择

select 
ExerciseCultures.Name,
max (convert (decimal (10,2), cast(weight as float))) as [Max Weight (KG)]
from workoutsessions
        join ExerciseCultures on workoutsessions.ExerciseID 
        = ExerciseCultures.ExerciseID
group by ExerciseCultures.Name

返回 31 行，每个锻炼 ID 一行，显示每一项的最高“权重”值。

我需要的是一个附加列，它还显示每一行的日期。日期列已经是锻炼 session 表中每行旁边的一列。

我尝试使用以下内容添加此日期列:

select 
ExerciseCultures.Name,
max (convert (decimal (10,2), cast(weight as float))) as [Max Weight (KG)],
workoutsessions.date
from workoutsessions
        join ExerciseCultures on workoutsessions.ExerciseID 
        = ExerciseCultures.ExerciseID
group by ExerciseCultures.Name,
    workoutsessions.date

但是这会返回 286 行——父表中的所有行。我需要的是原始查询结果以及锻炼 session 表中各自的日期。

有什么想法吗？

非常感谢

Answer 1

;WITH CTE AS
(
    SELECT  e.Name,
            CONVERT(DECIMAL(10,2),CAST(weight AS FLOAT))) [Max Weight (KG)],
            w.[date],
            RN = ROW_NUMBER() OVER( PARTITION BY e.name 
                                    ORDER BY CONVERT(DECIMAL(10,2),CAST(weight AS FLOAT))) DESC)
    FROM workoutsessions w
    INNER JOIN ExerciseCultures e
        ON w.ExerciseID = e.ExerciseID
)
SELECT name, [Max Weight (KG)], [date]
FROM CTE
WHERE RN = 1