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

My table is a list of Scheduled TV Programmes for multiple days and channels.

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

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

The result set I'm trying to create is What's on Now and What's on Next.

我想要创建的结果集是什么在现在和接下来是什么。

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

So something like:

所以类似于:

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

I've been racking my brain for the best way to do this without having to hard code an individual query for each channel. I think I've got the the overthinking it stage now so it would be great if someone can point me in the right direction.

我一直绞尽脑汁寻找最好的方法,而不必为每个频道硬编码单独的查询。我想我现在已经过时了,所以如果有人能指出我正确的方向,那就太棒了。

Thanks

PS: If it makes a difference I'm on Microsoft SQL Server 2012

PS:如果它有所作为我在Microsoft SQL Server 2012上

5 个解决方案

#1

This really seems like something you would let your GIU format and pivot but here's my go at it.

这真的看起来像你会让你的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

Edit: I'm only using time here but just add date. You should really consider combining the date and time columns.

编辑:我只在这里使用时间,但只是添加日期。您应该考虑组合日期和时间列。

Edit2: To add date just replace the time compare with this. Should even work over date boundaries.

编辑2:添加日期只需替换与此比较的时间。甚至应该在日期边界上工作。

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

#2

Here is the SQLFiddle example. This query also works well if a program starts in the previous day and ends in the current or starts in the current and ends in the next day (for NOW and NEXT results).

这是SQLFiddle示例。如果程序在前一天开始并在当前结束或从当前开始并在第二天结束(对于NOW和NEXT结果),则此查询也可以正常工作。

For current date just replace cast('09/19/2012 07:15' as datetime) with the CURRENT_TIMESTAMP:

对于当前日期,只需使用CURRENT_TIMESTAMP替换cast('09 / 19/2012 07:15'作为日期时间):

with T as 
(select channel, title, 
       (date+cast(Time as datetime )) as D_Time 
       from Scheduled_Programmes
)

select nn,
   max(CASE when channel=1 then Title end) as Chanel1, 
   max(CASE when channel=2 then Title end) as Chanel2,
   max(CASE when channel=3 then Title end) as Chanel3


from
(
select 'NOW' nn,Channel,Title,D_time,
    row_number() over (PARTITION by channel 
                      order by D_time desc) rn 
    from T
    where D_time<=cast('09/19/2012 07:15' as datetime)
union
select 'NEXT'nn,Channel,Title,D_time,
     row_number() over (PARTITION by channel 
                        order by D_time asc) rn 
     from T
     where D_time>cast('09/19/2012 07:15' as datetime)
) ATable where rn=1
group by nn
order by nn desc;

#3

select distinct Channel
, (
select top 2 Title from 
        (
         select Title
         from [Scheduled_Programmes] s
         where s.Channel = o.Channel
         and Date >='2012-09-19' and Time >='07:15' 
         group by Channel
         order by Date, Time desc
        ) x
) as Prog
from [Scheduled_Programmes] o

I have not tried it (due to unavailability of SQL Server at this point ), but this should work.

我没有尝试过(由于此时SQL Server不可用),但这应该可行。

#4

To get the data and cross-tab it use the following. You can rename the channel columns in the first select, but you do have to specify the valid channels in the pivot list.

要获取数据和交叉表,请使用以下内容。您可以在第一个选择中重命名通道列,但必须在数据透视表列表中指定有效通道。

N.b. The code could be simplified by combining the date and time fields to a single datetime field.

注:通过将日期和时间字段组合到单个日期时间字段,可以简化代码。

declare @current_date datetime = '20120919 07:15'

select *
from (
  select whenon, a1.channel, title from (
  select a.channel, MAX(CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) as DateTime, 'now' as whenon
  from Scheduled_Programmes a
  join (
    select channel, MIN(CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) as NextTime
    from Scheduled_Programmes 
    where CAST(Date AS DATETIME) + CAST(Time AS DATETIME) > @current_date
    group by channel
  ) b on a.channel = b.channel and CAST(Date AS DATETIME) + CAST(Time AS DATETIME) < NextTime
  group by a.channel
  union
  select channel, MIN(CAST(Date AS DATETIME) + CAST(Time AS DATETIME)), 'next' from Scheduled_Programmes where CAST(Date AS DATETIME) + CAST(Time AS DATETIME) > @current_date
  group by channel

  ) a1
  join Scheduled_Programmes b1 on a1.channel = b1.channel and a1.DateTime = CAST(Date AS DATETIME) + CAST(Time AS DATETIME)
) sub
pivot (max(title) for channel in ([1], [2], [3])) pivottable
order by whenon desc

#5

Another possible option - calculate the time difference & fetch the smallest +ve value (next) and smallest -ve value (now) - assumes DATE & TIME type fields, also returns minutes since start/until start.

另一种可能的选择 - 计算时差并获取最小+ ve值(下一个)和最小-ve值(现在) - 假设DATE&TIME类型字段,也返回自开始/开始以来的分钟数。

;with T(Channel, Date, Time, Title, delta) as (
select 
    *,
    datediff(minute, '19 sep 2012 07:15:00', dateadd(day, datediff(day, 0, date), cast(time as datetime2)))
from 
    Scheduled_Programmes
)
select 
case when T.delta >= 0 then 'Next' else 'Now' end,
T.* 
from T
inner join (
    select
        channel,
        min(case when delta > 0 then delta end) as starts_in,
        max(case when delta < 0 then delta end) as started
    from t
    group by channel
) T2
    on T.Channel = T2.Channel and (t.delta = T2.starts_in or t.delta=T2.started)
order by Channel, delta

#1