求算法:一段时间段内的星期几到星期几的集合

ohyear 2009-03-31 06:25:26

假设时间段为2009-04-01到2009-04-30.
我想得到这段时间内,每逢周一至周二的日期,要返回的结果集可以为DataTable(两列-周二日期和周三日期),或是二维数组(周二日期,周三日期)

另外:因为4月1号是星期三,所以周一至周二只有如下数据:
周一周二
2009-04-06 2009-04-07
2009-04-13 2009-04-14
2009-04-20 2009-04-21
2009-04-27 2009-04-28

即,超过起始日期的星期几不算在内.

使用C#或是Sql给的算法都行....
在线急等..先谢了!

...全文

182 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

ohyear 2009-03-31

打赏
举报

感谢小梁.结贴了!

htl258_Tony 2009-03-31

打赏
举报

declare @bt datetime,@et datetime

select @bt='2009-04-01',@et='2009-04-30'

select a.dt 周一,b.dt 周二

from

   (

     select dt=dateadd(dd,number,@bt) from master..spt_values where type='p' and number<=datediff(dd,@bt,@et) and datepart(w,dateadd(dd,number,@bt)+@@datefirst-1)=1

   ) a,

   (

     select dt=dateadd(dd,number,@bt) from master..spt_values where type='p' and number<=datediff(dd,@bt,@et) and datepart(w,dateadd(dd,number,@bt)+@@datefirst-1)=2

   ) b

where a.dt+1=b.dt



/*

周一                      周二

----------------------- -----------------------

2009-04-06 00:00:00.000 2009-04-07 00:00:00.000

2009-04-13 00:00:00.000 2009-04-14 00:00:00.000

2009-04-20 00:00:00.000 2009-04-21 00:00:00.000

2009-04-27 00:00:00.000 2009-04-28 00:00:00.000



(4 行受影响)

*/

Teng_s2000 2009-03-31

打赏
举报

[Quote=引用 10 楼 HEROWANG 的回复:]
小梁真乃神人也
[/Quote]
太支持了啊

牛死了啊！收藏了啊

rex100 2009-03-31

打赏
举报

帮顶

让你望见影子的墙 2009-03-31

打赏
举报

小梁真乃神人也

ws_hgo 2009-03-31

打赏
举报

此贴收藏学习下

ks_reny 2009-03-31

打赏
举报

小梁真是强人。

liangCK 2009-03-31

打赏
举报

偶的代码是用0来表示周日的，所以在传周日的参数时注意一下，

写得差不多了，只要在前台程序里写好参数，就可以传进去调用了。

liangCK 2009-03-31

打赏
举报

CREATE PROC 

    p @start_dt DATETIME,

    @end_dt DATETIME,

    @weekdays VARCHAR(20)

AS

    DECLARE @sSQL NVARCHAR(4000);

    DECLARE @columns NVARCHAR(1000)

    SELECT @sSQL='',@columns='';

    

    CREATE TABLE #weekday_table(ID TINYINT IDENTITY,[weekday] INT);

    INSERT #weekday_table([weekday])

        SELECT

            SUBSTRING(@weekdays,number,

                        CHARINDEX(',',@weekdays+',',number)-number)

        FROM master.dbo.spt_values AS A

        WHERE A.type='p' 

            AND number BETWEEN 1 AND LEN(@weekdays)

            AND SUBSTRING(','+@weekdays,number,1)=',';

            

    SELECT

        @columns=@columns+N',MAX(CASE WHEN [weekday]='+

                          RTRIM([weekday])+N' THEN dt END) AS [周'+

                            CASE [weekday] 

                                WHEN 0 THEN N'日'

                                WHEN 1 THEN N'一'

                                WHEN 2 THEN N'二'

                                WHEN 3 THEN N'三'

                                WHEN 4 THEN N'四'

                                WHEN 5 THEN N'五'

                                WHEN 6 THEN N'六' END +N']'

    FROM #weekday_table;

    

    SET @columns=STUFF(@columns,1,1,'');

    

    SET @sSQL=N'

        SELECT '+@columns+N'

        FROM (

            SELECT

               DATEADD(day,number,@start_dt) AS dt,

               (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],

               DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]

            FROM master.dbo.spt_values AS A

                JOIN #weekday_table AS B

                    ON (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7=B.[weekday]

            WHERE A.type=''p'' 

            AND DATEADD(day,number,@start_dt)<=@end_dt

        ) AS A

        GROUP BY [week]

    ';

    

    EXEC sp_executesql @sSQL,

             N'@start_dt DATETIME,@end_dt DATETIME',

             @start_dt,@end_dt;

             

GO



EXEC p '2009-04-01','2009-04-30','0,1,2,3,4,5,6'



GO

DROP PROC p



/*

周日                      周一                      周二                      周三                      周四                      周五                      周六

----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

NULL                    NULL                    NULL                    2009-04-01 00:00:00.000 2009-04-02 00:00:00.000 2009-04-03 00:00:00.000 2009-04-04 00:00:00.000

2009-04-05 00:00:00.000 2009-04-06 00:00:00.000 2009-04-07 00:00:00.000 2009-04-08 00:00:00.000 2009-04-09 00:00:00.000 2009-04-10 00:00:00.000 2009-04-11 00:00:00.000

2009-04-12 00:00:00.000 2009-04-13 00:00:00.000 2009-04-14 00:00:00.000 2009-04-15 00:00:00.000 2009-04-16 00:00:00.000 2009-04-17 00:00:00.000 2009-04-18 00:00:00.000

2009-04-19 00:00:00.000 2009-04-20 00:00:00.000 2009-04-21 00:00:00.000 2009-04-22 00:00:00.000 2009-04-23 00:00:00.000 2009-04-24 00:00:00.000 2009-04-25 00:00:00.000

2009-04-26 00:00:00.000 2009-04-27 00:00:00.000 2009-04-28 00:00:00.000 2009-04-29 00:00:00.000 2009-04-30 00:00:00.000 NULL                    NULL





(5 行受影响)

*/

liangCK 2009-03-31

打赏
举报

CREATE PROC 

    p @start_dt DATETIME,

    @end_dt DATETIME,

    @weekdays VARCHAR(20)

AS

    DECLARE @sSQL NVARCHAR(4000);

    DECLARE @columns NVARCHAR(1000)

    SELECT @sSQL='',@columns='';

    

    CREATE TABLE #weekday_table(ID TINYINT IDENTITY,[weekday] INT);

    INSERT #weekday_table([weekday])

        SELECT

            SUBSTRING(@weekdays,number,

                        CHARINDEX(',',@weekdays+',',number)-number)

        FROM master.dbo.spt_values AS A

        WHERE A.type='p' 

            AND number BETWEEN 1 AND LEN(@weekdays)

            AND SUBSTRING(','+@weekdays,number,1)=',';

            

    SELECT

        @columns=@columns+N',MAX(CASE WHEN [weekday]='+

                          RTRIM([weekday])+N' THEN dt END) AS [周'+RTRIM([weekday])+N']'

    FROM #weekday_table;

    

    SET @columns=STUFF(@columns,1,1,'');

    

    SET @sSQL=N'

        SELECT '+@columns+N'

        FROM (

            SELECT

               DATEADD(day,number,@start_dt) AS dt,

               (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],

               DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]

            FROM master.dbo.spt_values AS A

                JOIN #weekday_table AS B

                    ON (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7=B.[weekday]

            WHERE A.type=''p'' 

            AND DATEADD(day,number,@start_dt)<=@end_dt

        ) AS A

        GROUP BY [week]

    ';

    

    EXEC sp_executesql @sSQL,

             N'@start_dt DATETIME,@end_dt DATETIME',

             @start_dt,@end_dt;

             

GO



EXEC p '2009-04-01','2009-04-30','1,2'



GO

DROP PROC p



/*

周1                      周2

----------------------- -----------------------

2009-04-06 00:00:00.000 2009-04-07 00:00:00.000

2009-04-13 00:00:00.000 2009-04-14 00:00:00.000

2009-04-20 00:00:00.000 2009-04-21 00:00:00.000

2009-04-27 00:00:00.000 2009-04-28 00:00:00.000



(4 行受影响)





*/

ohyear 2009-03-31

打赏
举报

感谢liangCK先.

周一,周二我想要传参数进去的,这里只是举个例子说是周一到周二,也有可能其它的.

liangCK 2009-03-31

打赏
举报

--2000

DECLARE @start_dt DATETIME,@end_dt DATETIME;

SELECT @start_dt='2009-04-01',@end_dt='2009-04-30';



SELECT 

    MAX(CASE WHEN [weekday]=1 THEN dt END) AS 周一,

    MAX(CASE WHEN [weekday]=2 THEN dt END) AS 周二

FROM (

   SELECT

       DATEADD(day,number,@start_dt) AS dt,

       (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],

       DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]

   FROM master.dbo.spt_values AS A

   WHERE A.type='p' 

       AND DATEADD(day,number,@start_dt)<=@end_dt

       AND (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 IN(1,2)

) AS A

GROUP BY [week]





/*

周一                      周二

----------------------- -----------------------

2009-04-06 00:00:00.000 2009-04-07 00:00:00.000

2009-04-13 00:00:00.000 2009-04-14 00:00:00.000

2009-04-20 00:00:00.000 2009-04-21 00:00:00.000

2009-04-27 00:00:00.000 2009-04-28 00:00:00.000



(4 行受影响)



*/

liangCK 2009-03-31

打赏
举报

DECLARE @start_dt DATETIME,@end_dt DATETIME;

SELECT @start_dt='2009-04-01',@end_dt='2009-04-30';



SELECT 

    [1] AS 周一,

    [2] AS 周二

FROM (

   SELECT

       DATEADD(day,number,@start_dt) AS dt,

       (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],

       DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]

   FROM master.dbo.spt_values AS A

   WHERE A.type='p' 

       AND DATEADD(day,number,@start_dt)<=@end_dt

       AND (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 IN(1,2)

) AS A

    PIVOT(MAX(dt) FOR [weekday] IN([1],[2])) AS pvt





/*

周一                      周二

----------------------- -----------------------

2009-04-06 00:00:00.000 2009-04-07 00:00:00.000

2009-04-13 00:00:00.000 2009-04-14 00:00:00.000

2009-04-20 00:00:00.000 2009-04-21 00:00:00.000

2009-04-27 00:00:00.000 2009-04-28 00:00:00.000



(4 行受影响)

*/