sql-server - 现在和下一个电视节目信息SQL查询

Question

我的表格是多天和 channel 的预定电视节目列表。

SELECT * FROM [Scheduled_Programmes]

Channel   Date          Time     Title
1         2012-09-19    06:00    Family Guy
2         2012-09-19    06:01    CSI Miami
3         2012-09-19    06:20    News
1         2012-09-19    06:30    Heroes
2         2012-09-19    07:01    Spiderman
3         2012-09-19    06:40    Batman
1         2012-09-19    07:30    Micky Mouse
2         2012-09-19    07:31    CSI New York
3         2012-09-19    07:10    Friends
1         2012-09-19    07:55    The Wire
2         2012-09-19    08:00    Dodgeball
3         2012-09-19    07:35    Gossip Girl

我尝试创建的结果集是“What's on Now”和“What's on Next”。

Let's assume the current datetime is (D/M/Y HH:MM) 19/09/2012 07:15 

所以类似:

          Channel 1     Channel 2       Channel 3
NOW       Heroes        Spiderman       Friends  
NEXT      Micky Mous    CSI New York    Gossip Girl

我一直在绞尽脑汁寻找最好的方法来做到这一点，而不必为每个 channel 硬编码单独的查询。我想我现在已经到了想太多的阶段 因此，如果有人能指出我正确的方向，那就太好了。

谢谢

PS:如果有影响的话，我使用的是 Microsoft SQL Server 2012

Answer 1

这看起来确实像是您可以让 GIU 格式化和旋转的东西，但这是我的尝试。

SELECT * FROM (
    SELECT * FROM (
        SELECT X.Status, X.Channel, X.Title FROM (
        SELECT 'NOW' as Status, Channel, Title, RANK() OVER (PARTITION BY Channel ORDER BY Time DESC) RANKED FROM Sceduled_Programs SP
        WHERE DateTime <= '7:15') X
        WHERE X.RANKED = 1
    ) A
    UNION ALL
        SELECT * FROM (
        SELECT Y.Status, Y.Channel, Y.Title FROM (
        SELECT 'NEXT' as Status, Channel, Title, RANK() OVER (PARTITION BY Channel ORDER BY Time ASC) RANKED FROM Sceduled_Programs SP
        WHERE DateTime > '7:15') Y
        WHERE Y.RANKED = 1
    ) B
) DataToPivot
PIVOT (MAX(Title) FOR Channel IN ([1], [2], [3])) AS PivotTable

编辑:我在这里只使用时间，但只添加日期。您确实应该考虑合并日期和时间列。

编辑2:要添加日期，只需替换时间与此比较即可。甚至应该跨越日期界限。

WHERE CAST(Date AS DATETIME) + CAST(Time AS DATETIME) > '19/09/2012 07:15'