SQL“转换"询问 [英] SQL "transform" query
本文介绍了SQL“转换"询问的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在表上有这些数据(使用 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屋!
查看全文