如何针对此场景编写SP？
如何针对此场景编写SP？
提问于 2011-06-01 23:09:42
我在数据库里有一个这样的表
SlNo
Driver_Id
Sun_StartTime
Sun_EndTime
Mon_StartTime
Mon_EndTime
Tue_StartTime
Tue_EndTime
Wed_StartTime
Wed_EndTime
Thur_StartTime
Thur_EndTime
Fri_StartTime
Fri_EndTime
Sat_StartTime
Sat_EndTime以上是我的数据库中的字段
但是在检索的时候，我必须像这样检索
Driver_Id | Sun_StartTime | Sun_EndTime
Driver_Id | Mon_StartTime | Mon_EndTime
Driver_Id | Tue_StartTime | Tue_EndTime
Driver_Id | Wed_StartTime | Wed_EndTime
Driver_Id | Thur_StartTime | Thur_EndTime
Driver_Id | Fri_StartTime | Fri_EndTime
Driver_Id | Sat_StartTime | Sat_EndTime也就是说，我必须将一条记录拆分为7条记录，如何使用存储过程来做到这一点。请帮我提前谢谢
回答 3
Stack Overflow用户
发布于 2011-06-01 23:19:48
您可以使用Union查询来获得这种类型的结果。但是你没有提到where子句。
Create Proc testing
AS
Select
driverID, Mon_StartTime,Mon_EndTime From YourTableName
Union
Select
driverID, Tue_EndTime, Tue_EndTime From YourTableName
Union
Select
driverID, Wed_StartTime, Wed_EndTime From YourTableName
Union
Select
driverID, Thur_StartTime, Thur_EndTime From YourTableName
Union
Select
driverID, Fri_StartTime, Fri_EndTime From YourTableName
Union
Select
driverID, Sat_StartTime, Sat_EndTime From YourTableName
Union
Select
driverID, Sun_StartTime, Sun_EndTime From YourTableNameStack Overflow用户
发布于 2011-06-01 23:26:07
像这样的事情应该会让你开始朝着正确的方向前进。
SELECT Driver_Id, Sun_StartTime AS StartTime, Sun_EndTime as EndTime, 1 AS DayOfWeek
FROM YourTable
WHERE Driver_Id = @YourDriverId
UNION ALL
SELECT Driver_Id, Mon_StartTime AS StartTime, Mon_EndTime as EndTime, 2 AS DayOfWeek
FROM YourTable
WHERE Driver_Id = @YourDriverId
UNION ALL
...
SELECT Driver_Id, Sat_StartTime AS StartTime, Sat_EndTime as EndTime, 7 AS DayOfWeek
FROM YourTable
WHERE Driver_Id = @YourDriverId
ORDER BY DayOfWeekStack Overflow用户
发布于 2011-06-01 23:29:51
看看这个：http://wiki.lessthandot.com/index.php/Column_To_Row_%28UNPIVOT%29
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6203399
复制相关文章
相似问题
腾讯云开发者
