SQL“转换"询问 [英] SQL "transform" query

问题描述

我在表上有这些数据(使用 SQL Server 2005):

I have these data on a table (using SQL Server 2005):

ID    ParentID    StartTime    EndTime
77    62          08:00:00     11:00:00
78    62          12:00:00     15:00:00
79    62          18:00:00     22:00:00

我想把它改成这样:

ParentID  BreakfastStart  BreakfastEnd  LunchStart  LunchEnd  DinnerStart  DinnerEnd
62        08:00:00        11:00:00      12:00:00    15:00:00  18:00:00     22:00:00

现在困难的部分是:假设我没有其他数据字段指定哪个记录是早餐、午餐或晚餐.我想将它们与最低开始时间相关联，即开始时间较低的是早餐，下一个较低的是午餐，较高的是晚餐(假设所有三个(并且只有三个)记录总是被填满).

Now the hard part is: assume I have no other data field specifying which record is breakfast, lunch or dinner. I want to associate them with lowest start time, i.e., the lower start time will be breakfast, next lower will be lunch and the higher will be dinner (assume all three (and only three) records are always filled).

有什么想法吗?

推荐答案

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY parentID ORDER BY StartTime) AS rn
        FROM    mytable
        )
SELECT  qb.ParentID,
        qb.StartTime AS BreakfastStart, qb.EndTime AS BreakfastEnd,
        ql.StartTime AS LunchStart, ql.EndTime AS LunchEnd,
        qd.StartTime AS DinnerStart, qd.EndTime AS DinnerEnd
FROM    q qb
LEFT JOIN
        q ql
ON      ql.parentID = qb.parentID
        AND ql.rn = 2
LEFT JOIN
        q qd
ON      qd.parentID = qb.parentID
        AND qd.rn = 3
WHERE   qb.rn = 1

这篇关于SQL“转换"询问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！