查看内容

SQL打印全年日历

  • 2020-02-09 12:35
  • 数据库知识
  • Views

数据库碰着:SQL SEHighlanderVEGL450 2010Highlander2

自家此前有写过打字与印刷前些日子日历的SQL,里头有详实的求证。具体请参见后面包车型客车博文——生开支月日历。

全年日历只是在前些时间日历的根基上加了月音讯,并按月度分组求得。

上面直接分享SQL

/*基础数据:年初日期,全年有多少天*/WITH  x0     AS ( SELECT  CONVERT(DATE, '2015-01-01') AS yearbegin ,            CONVERT(DATE, '2015-12-31') AS yearend ,            DATEDIFF(DAY, '2015-01-01', '2015-12-31') AS dayscount       ),/*枚举全年的所有日期*/    x1     AS ( SELECT  DATEADD(DAY, number, yearbegin) AS ndate        FROM   x0 ,            master.dbo.spt_values spt        WHERE  spt.type = 'P'            AND spt.number >= 0            AND spt.number <= dayscount       ),/*罗列全年日期对应的月份,第几周,星期几,本月第几天*/    x2     AS ( SELECT  ndate ,            DATEPART(month, ndate) AS nmonth ,            DATEPART(week, ndate) AS nweek ,            DATEPART(weekday, ndate) AS nweekday ,            DATEPART(day, ndate) AS nday        FROM   x1       ),/*按月份、所在周分组,生成全年日历*/    x3     AS ( SELECT  nmonth ,            nweek ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 1 THEN nday                    END) AS VARCHAR(2)), '') AS 日 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 2 THEN nday                    END) AS VARCHAR(2)), '') AS 一 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 3 THEN nday                    END) AS VARCHAR(2)), '') AS 二 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 4 THEN nday                    END) AS VARCHAR(2)), '') AS 三 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 5 THEN nday                    END) AS VARCHAR(2)), '') AS 四 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 6 THEN nday                    END) AS VARCHAR(2)), '') AS 五 ,            ISNULL(CAST(MAX(CASE nweekday                     WHEN 7 THEN nday                    END) AS VARCHAR(2)), '') AS 六        FROM   x2        GROUP BY nmonth ,            nweek       )/*将月份相同的值只在第一行显示*/  SELECT REPLACE(CASE WHEN ROW_NUMBER() OVER ( PARTITION BY nmonth ORDER BY nweek ) = 1             THEN nmonth             ELSE 0          END, 0, '') AS 月份 ,      日 ,      一 ,      二 ,      三 ,      四 ,      五 ,      六  FROM  x3

代码不算多,60多行,而且能够领悟。假设以为把“周六”放在第一列有一些别扭,能够x第22中学生成所在周时对星期天

做一些极其管理就足以了。

(本文完)

上一篇:两表合并查询澳门太阳集团2007网站 下一篇:没有了