在MSSQL中按一栏排序

2024-05-10 • 问答

我有以下SQL表：

[日历]

[CalendarId]
[Name]

样本数据：

CalendarId  ResourceKey    Name
1           1              tk1-Room1
2           2              tk1-Room2
3           3              tk1-noentries

[CalendarEntry]

[CalendarId]
[CalendarEntryId]
[Start]
[End]

样本数据：

CalendarId  Start                            End
1           2019-11-18 16:00:00.0000000      2019-11-18 17:00:00.0000000
1           2019-11-19 16:00:00.0000000      2019-11-19 17:00:00.0000000
2           2019-11-25 16:00:00.0000000      2019-11-25 17:00:00.0000000
1           2019-11-25 17:00:00.0000000      2019-11-25 18:00:00.0000000

预期输出：

Name             StartDate             EndDate                ResourceKey
tk1-Room1        2019-11-25 17:00:00   2019-11-25 17:00:00    1
tk1-Room2        2019-11-25 16:00:00   2019-11-25 17:00:00    2
tk1-noentries    NULL                  NULL                   3

我正在尝试列出所有Calendar条目，以及它们对应的开始（最近）和结束时间。

我有以下代码可以部分工作：

SELECT Name,StartDate,ResourceKey FROM [Calendar].[dbo].[Calendar] CAL
LEFT JOIN(
    SELECT 
        CalendarId,MAX(ENT.[Start]) as StartDate
    FROM [CalendarEntry] ENT
    GROUP BY CalendarId
    )
AS ST on CAL.CalendarId = ST.CalendarId

但是，如果我要包括该列，请在我的子SELECT中，例如EG：

    SELECT 
        CalendarId,MAX(ENT.[Start]) as StartDate,ENT.[End] as endDate

我收到以下错误：

Column 'CalendarEntry.End' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

但是，现在将其包含在GROUP BY中会导致每个日历返回多个CalendarEntry行。

对我来说，从CalendarEntry中获取最新行以允许我访问所有列的最佳方法是什么？

谢谢！

dongjiahui 回答：在MSSQL中按一栏排序

这是每组问题中通常排名前1的问题。

您可以使用row_number()：

select *
from (
    select 
        c.*,e.*,row_number() over(partition by c.CalendarId order by e.Start desc) rn
    from [Calendar].[dbo].[Calendar] c
    left join [CalendarEntry] e ON c.CalendarId = e.CalendarId
) t
where rn = 1

或者您可以使用相关子查询进行过滤：

select c.*,e.*
from [Calendar].[dbo].[Calendar] c
left join [CalendarEntry] e 
    on c.CalendarId = e.CalendarId
    and c.Start = (
        select max(e1.Start) from [CalendarEntry] e where c.CalendarId = e1.CalendarId
    )

我正在尝试列出所有日历条目，以及它们对应的开始（最近）和结束时间。

我将其解释为CalendarEntry中每个CalendarId的最新记录：

select ce.*
from CalendarEntry ce
where ce.StartDate = (select max(ce2.StartDate)
                      from CalendarEntry ce2
                      where ce2.CalendarId = ce.CalendarId
                     );

您也可以尝试OUTER APPLY，但是@GMB的答案从性能预期的角度来看是更好的方法

SELECT Name,StartDate,EndDate,ResourceKey
FROM dbo.Calendar AS C
    OUTER APPLY
(
    SELECT TOP 1 *
    FROM dbo.CalendarEntry
    WHERE CalendarId = C.CalendarId
    ORDER BY StartDate DESC,EndDate DESC
) AS K;

您也可以尝试使用LAST_VALUE / FIRST_VALUE（在SQL Server 2012及更高版本中提供）以下功能，但是@GMB的答案还是一种性能更好的方法预期的：

SELECT DISTINCT
       Name,LAST_VALUE(StartDate) OVER (PARTITION BY C.CalendarId
                                   ORDER BY StartDate,EndDate
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),LAST_VALUE(EndDate) OVER (PARTITION BY C.CalendarId
                                 ORDER BY StartDate,EndDate
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),ResourceKey
FROM dbo.Calendar AS C
    LEFT JOIN dbo.CalendarEntry
        ON CalendarEntry.CalendarId = C.CalendarId;

如果要使用FIRST_VALUE函数，则应按以下方式重写订单：

ORDER BY StartDate DESC,EndDate DESC

而且您也不需要指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING部分

SELECT DISTINCT
       Name,FIRST_VALUE(StartDate) OVER (PARTITION BY C.CalendarId
                                   ORDER BY StartDate DESC,EndDate DESC),FIRST_VALUE(EndDate) OVER (PARTITION BY C.CalendarId
                                 ORDER BY StartDate DESC,ResourceKey
FROM dbo.Calendar AS C
    LEFT JOIN dbo.CalendarEntry
        ON CalendarEntry.CalendarId = C.CalendarId;

greatest-n-per-group sql sql-server tsql

本文链接：https://www.f2er.com/3036319.html

在MSSQL中按一栏排序

dongjiahui 回答：在MSSQL中按一栏排序

大家都在问