如何为此方案编写SP? [英] How to write SP for this Scenario?

问题描述

大家好，
我的数据库中有一个这样的表.

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

以上是我数据库中的字段，但是在检索时，我必须像这样检索:-

driverID | Sun_StartTime | Sun_EndTime
driverID | Mon_StartTime | Mon_EndTime
driverID | Tue_StartTime | Tue_EndTime
driverID | Wed_StartTime | Wed_EndTime
driverID | Thur_StartTime | Thur_EndTime
driverID | Fri_StartTime | Fri_EndTime
driverID | Sat_StartTime | Sat_EndTime

那就是我必须将一个记录分成7个记录.

如何使用存储过程执行此操作.

请帮帮我
在此先感谢

Hi all,
I have a table like this in database.

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

The above are the fields in my database, but while retriving I have to retrive like:-

driverID | Sun_StartTime | Sun_EndTime
driverID | Mon_StartTime | Mon_EndTime
driverID | Tue_StartTime | Tue_EndTime
driverID | Wed_StartTime | Wed_EndTime
driverID | Thur_StartTime | Thur_EndTime
driverID | Fri_StartTime | Fri_EndTime
driverID | Sat_StartTime | Sat_EndTime

That is I have to split one record into 7 records.

How can I do this using stored procedure.

Please help me
Thanks in advance

推荐答案

您好，

希望对您有帮助，

这是将1行拆分为7的方法....

Hi there,

I hope this helps you,

This is how to split 1 row into 7....

SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Sunday' as TheDay, Table_1.Sun_StartTime as StartTime,Table_1.Sun_EndTime as EndTime
FROM	 Table_1
WHERE     (Table_1.Sun_StartTime IS NOT NULL) AND (Table_1.Sun_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.Driver_Id, 'Monday' as TheDay,Table_1.Mon_StartTime,Table_1.Mon_EndTime
FROM	 Table_1
WHERE     (Table_1.Mon_StartTime IS NOT NULL) AND (Table_1.Mon_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Tuesday' as TheDay, Table_1.Tue_StartTime,Table_1.Tue_EndTime

FROM	 Table_1
WHERE     (Table_1.Tue_StartTime IS NOT NULL) AND (Table_1.Tue_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Wednesday' as TheDay, Table_1.Wed_StartTime,Table_1.Wed_EndTime
FROM	 Table_1
WHERE     (Table_1.Wed_StartTime IS NOT NULL) AND (Table_1.Wed_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Thursday' as TheDay, Table_1.Thu_StartTime,Table_1.Thu_EndTime
FROM	 Table_1
WHERE     (Table_1.Thu_StartTime IS NOT NULL) AND (Table_1.Thu_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Friday' as TheDay, Table_1.Fri_StartTime,Table_1.Fri_EndTime
FROM	 Table_1
WHERE     (Table_1.Fri_StartTime IS NOT NULL) AND (Table_1.Fri_EndTime IS NOT NULL)

UNION ALL
SELECT     TOP (100) PERCENT Table_1.Driver_Id,'Saturday' as TheDay, Table_1.Sat_StartTime,Table_1.Sat_EndTime
FROM	 Table_1
WHERE     (Table_1.Sat_StartTime IS NOT NULL) AND (Table_1.Sat_EndTime IS NOT NULL)

因为我感觉好/无聊...这是存储过程

Because I''m feeling kind / bored.... Here is the Stored Proc

CREATE PROCEDURE MyProc 
	-- Add the parameters for the stored procedure here
	@SINO int = 0, 
	@TheDay varchar(10) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	with TempIdea (SINO, Driver_Id, TheDay,StartTime,EndTime) as (
SELECT     TOP (100) PERCENT Table_1.SINO, Table_1.Driver_Id,'Sunday' as TheDay, Table_1.Sun_StartTime as StartTime,Table_1.Sun_EndTime as EndTime
FROM	 Table_1
WHERE     (Table_1.Sun_StartTime IS NOT NULL) AND (Table_1.Sun_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id, 'Monday' as TheDay,Table_1.Mon_StartTime,Table_1.Mon_EndTime
FROM	 Table_1
WHERE     (Table_1.Mon_StartTime IS NOT NULL) AND (Table_1.Mon_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Tuesday' as TheDay, Table_1.Tue_StartTime,Table_1.Tue_EndTime

FROM	 Table_1
WHERE     (Table_1.Tue_StartTime IS NOT NULL) AND (Table_1.Tue_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Wednesday' as TheDay, Table_1.Wed_StartTime,Table_1.Wed_EndTime
FROM	 Table_1
WHERE     (Table_1.Wed_StartTime IS NOT NULL) AND (Table_1.Wed_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Thursday' as TheDay, Table_1.Thu_StartTime,Table_1.Thu_EndTime
FROM	 Table_1
WHERE     (Table_1.Thu_StartTime IS NOT NULL) AND (Table_1.Thu_EndTime IS NOT NULL)

UNION ALL

SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Friday' as TheDay, Table_1.Fri_StartTime,Table_1.Fri_EndTime
FROM	 Table_1
WHERE     (Table_1.Fri_StartTime IS NOT NULL) AND (Table_1.Fri_EndTime IS NOT NULL)

UNION ALL
SELECT     TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Saturday' as TheDay, Table_1.Sat_StartTime,Table_1.Sat_EndTime
FROM	 Table_1
WHERE     (Table_1.Sat_StartTime IS NOT NULL) AND (Table_1.Sat_EndTime IS NOT NULL)

)

Select * from TempIdea 
Where SINO = @SINO AND TheDay = @TheDay

END

如果要返回7条记录而不是特定日期，只需从存储过程中删除"TheDay"参数即可.
然后，您将根据给定存储过程的SlNo返回7天.

一注...

我编写SQL的方式意味着开始时间和结束时间不能为null，否则它们将不会返回记录.因此，如果有开始时间，而结束时间为null，则它将不会检索该记录.

反正

祝你好运.

If you want to return 7 records and not the specific day, just remove the "TheDay" parameter from the Stored Procedure.
That will then return to you the 7 days based on the SlNo you give the Stored Procedure.

One note...

The way I have written the SQL means the Start and End time must not be null, otherwise they wont return a record. So if there is a start time and the end time is null, then it wont retrieve that record.

Anyways,

Good luck.

这篇关于如何为此方案编写SP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！