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

2009-03-31 06:25:26 247 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

由SQL数据库转换Redis的实例出租车查询热点区域部分（深圳市的士查询软件_gogo查的） 1、现有版本总结热点区域信息是根据过去半年的历史数据计算得出，短期内属于固定信息，不用做任何改变。现在所用的gogo查的版本中，查询热点区域所用的API，是先在servlet中计算出用户选择区域周围的区域ID，构造出SQL查询语句，最后从postgresql中读取所需的热点区域信息。 SQL语句如下： Sel 阅读详情

假设时间段为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给的算法都行....
在线急等..先谢了!

...全文

数学建模竞赛实战：从数据预处理到模型构建与论文写作全流程解析数学建模是运用数学语言和方法，通过抽象与简化，对实际问题进行描述、分析和求解的过程。其核心原理在于将现实世界的复杂系统映射为可计算的数学模型，涉及统计学、优化理论和算法设计等多个领域。这一技术价值在于为决策提供量化依据，提升效率与精准度，广泛应用于供应链管理、金融风控、交通规划等场景。在实践层面，数据预处理与特征工程是模型有效性的基石，而模型融合与灵敏度分析则能显著提升解决方案的稳健性。本文以高教社杯全国大学生数学建模竞赛为背景，深入剖析了从赛题解析、数据清洗、特征构建到模型选择、求解验证及论文撰写的完整实阅读详情

ohyear

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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 行受影响)

*/

ziyuan_1016 2009-03-31

打赏
举报

帮你顶！

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

相关推荐