Sql 语句小课堂6：使用开窗函数和系统表生成一个简单的日历

已于 2023-06-30 15:04:01 修改
阅读量855 收藏 2
点赞数 2
分类专栏： 数据库 文章标签： sql sqlserver 行转列 开窗函数 排名函数
于 2021-12-24 13:34:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/superwfei/article/details/122126044
版权

Sql 语句小课堂6：使用开窗函数和系统表生成一个简单的日历

额，这个部分是修改后追加的，因为质量分太低，所以水点内容，提一提质量分，嘿嘿。

系统表 spt_values

嗯，用来补全数据很常用的一个表，通常写作 master…spt_values，其中 type 为 p 的内容，有 0 到 2017 的连续整数，共2048个数字，对于数据补全缺失，可以结合 id 列或日期列之类的，补全数据的相关文章，可以参考《sqlserver 数据区间补全》

CTE 表

一个可以用来生成或者建立临时数据的方便的方法，当然，也不仅仅可以这么做，同样可以参考老顾的其他文章《【新星计划】数据库 CTE 初识》或自行百度。

CSDN 文盲老顾的博客，https://blog.csdn.net/superwfei
老顾的个人社区，https://bbs.csdn.net/forums/bfba6c5031e64c13aa7c60eebe858a5f?category=10003&typeId=3364713

排名函数

首先了解下 sqlserver 的开窗函数，有 row_number，rank，dense_rank，都可以对行数据进行排序并附加序号，关于row_number就不细说了，本文使用 dense_rank

关于排名函数，可以自行百度一下，也可以看老顾的文章《【新星计划】数据库 排名函数 初识》

日期函数

然后，我们在本文里复习一下日期函数的用法，dateadd，datediff，datepart

dateadd 用来对日期进行计算，比如前一天，后一天，前一小时后一小时之类的，当然，也可以前三十天，后一年之类的。

datediff 用来求两个日期数据的差，差的结果以指定的类型进行返回，比如日期差，分钟差，月份差之类的。

datepart 用来获取日期之间指定位置的数据，比如年、月、日、星期几之类的。

行转列

最后，使用行转列，完成日历的排布

行转列的时候，需要注意一点，如果出去需要转列的字段外，其他字段如果完全相同，则会合并成为同一行，如果所有其他列都不相同，则会每个数据占一行，如果部分相同，部分不同，则相同的合并为同一行，不同的则为多行，也就是本文日历实现的逻辑。

完整代码



declare @year int,@sd date,@ed date
-- 指定公历年份
set @year = 2021 
-- 获得当年的第一天与次年的第一天
select @sd = CONVERT(varchar,@year)+'-1-1',@ed=CONVERT(varchar,@year+1)+'-1-1'

;with t as (
	-- 先将所有日期补全出来
	select dateadd(d,number,@sd) as d 
	from master..spt_values
	where type='p'
	and number<DATEDIFF(D,@sd,@ed)
),t1 as (
	-- 取得每个日期所属的月份和星期数以及每周的第几天，需要注意，如果设置了星期天归属设置，输出内容可能有差异
	-- 使用 dense_rank 排名函数，对每个月的所有日期使用同一个排名，dense_rank与rank的区别在于一个连续，一个非连续
	select dense_rank() over(order by convert(varchar(6),d,112)) as m,DATEPART(week,d) w,DATEPART(weekday,d) as wd,DATEPART(day,d) as d 
	from t
)
select CONVERT(varchar,m)+'月' as 月份,'第'+convert(varchar,w)+'周' 周目
	,[1] 周日,[2] 周一,[3] 周二,[4] 周三,[5] 周四,[6] 周五,[7] 周六 
from t1 a
pivot(
	max(d) for wd in ([1],[2],[3],[4],[5],[6],[7])
)p


月份     周目       周日        周一        周二        周三        周四        周五        周六
------ -------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1月     第1周      NULL        NULL        NULL        NULL        NULL        1           2
1月     第2周      3           4           5           6           7           8           9
1月     第3周      10          11          12          13          14          15          16
1月     第4周      17          18          19          20          21          22          23
1月     第5周      24          25          26          27          28          29          30
1月     第6周      31          NULL        NULL        NULL        NULL        NULL        NULL
2月     第6周      NULL        1           2           3           4           5           6
2月     第7周      7           8           9           10          11          12          13
2月     第8周      14          15          16          17          18          19          20
2月     第9周      21          22          23          24          25          26          27
2月     第10周     28          NULL        NULL        NULL        NULL        NULL        NULL
3月     第10周     NULL        1           2           3           4           5           6
3月     第11周     7           8           9           10          11          12          13
3月     第12周     14          15          16          17          18          19          20
3月     第13周     21          22          23          24          25          26          27
3月     第14周     28          29          30          31          NULL        NULL        NULL
4月     第14周     NULL        NULL        NULL        NULL        1           2           3
4月     第15周     4           5           6           7           8           9           10
4月     第16周     11          12          13          14          15          16          17
4月     第17周     18          19          20          21          22          23          24
4月     第18周     25          26          27          28          29          30          NULL
5月     第18周     NULL        NULL        NULL        NULL        NULL        NULL        1
5月     第19周     2           3           4           5           6           7           8
5月     第20周     9           10          11          12          13          14          15
5月     第21周     16          17          18          19          20          21          22
5月     第22周     23          24          25          26          27          28          29
5月     第23周     30          31          NULL        NULL        NULL        NULL        NULL
6月     第23周     NULL        NULL        1           2           3           4           5
6月     第24周     6           7           8           9           10          11          12
6月     第25周     13          14          15          16          17          18          19
6月     第26周     20          21          22          23          24          25          26
6月     第27周     27          28          29          30          NULL        NULL        NULL
7月     第27周     NULL        NULL        NULL        NULL        1           2           3
7月     第28周     4           5           6           7           8           9           10
7月     第29周     11          12          13          14          15          16          17
7月     第30周     18          19          20          21          22          23          24
7月     第31周     25          26          27          28          29          30          31
8月     第32周     1           2           3           4           5           6           7
8月     第33周     8           9           10          11          12          13          14
8月     第34周     15          16          17          18          19          20          21
8月     第35周     22          23          24          25          26          27          28
8月     第36周     29          30          31          NULL        NULL        NULL        NULL
9月     第36周     NULL        NULL        NULL        1           2           3           4
9月     第37周     5           6           7           8           9           10          11
9月     第38周     12          13          14          15          16          17          18
9月     第39周     19          20          21          22          23          24          25
9月     第40周     26          27          28          29          30          NULL        NULL
10月    第40周     NULL        NULL        NULL        NULL        NULL        1           2
10月    第41周     3           4           5           6           7           8           9
10月    第42周     10          11          12          13          14          15          16
10月    第43周     17          18          19          20          21          22          23
10月    第44周     24          25          26          27          28          29          30
10月    第45周     31          NULL        NULL        NULL        NULL        NULL        NULL
11月    第45周     NULL        1           2           3           4           5           6
11月    第46周     7           8           9           10          11          12          13
11月    第47周     14          15          16          17          18          19          20
11月    第48周     21          22          23          24          25          26          27
11月    第49周     28          29          30          NULL        NULL        NULL        NULL
12月    第49周     NULL        NULL        NULL        1           2           3           4
12月    第50周     5           6           7           8           9           10          11
12月    第51周     12          13          14          15          16          17          18
12月    第52周     19          20          21          22          23          24          25
12月    第53周     26          27          28          29          30          31          NULL

(63 行受影响)
文盲老顾
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 7
    评论
专栏目录
SQL语句 根据月份产生出勤人员月份日历报表
01-17
一个SQL存储过程： 出勤资料查询 根据月份产生出勤人员月份日历报表 用到2个表：M_Users（用户表）、X_ITAttendance（用户出勤资料表），查询出的格式如桌面日历，日期不存在的以NULL值显示，存在的显示总的出勤时间（以小时计）。
SQL Server 开窗函数 Over()代替游标的使用详解
12-14
前言： 今天在优化工作中遇到的sql慢的问题，发现以前用了挺多游标来处理数据，这样就导致在数据量多的情况下，需要一行一行去遍历从而计算需要的数据，这样处理的结果就是数据慢，容易卡死。 语法介绍： 1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的 　　 2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作 例如：SUM() Over() 累加值、AVG() Over() 平均数 MAX() Over() 最大值、MIN() Over() 最小值 具体介绍： 下面模拟工作中通过开窗函数代替游标的例子，通过期初余额与单据的预收金额、应收金额
SQL Server 输出日历 (收集)
人生路上漫悠悠，程序写春秋...
07-06 1678
SQL Server 输出日历--源文收集于网络 方法一： create function fn_Calendar(@year int, @month int)returns nvarchar(max)asbegin    declare @result nvarchar(max), @Enter nvarchar(8)    select @Enter =
Oracle数据库中SQL开窗函数的使用
12-14
开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计 算的行集组是窗口。在 ISO SQL 规定
SQL中的开窗函数详解可代替聚合函数使用
12-14
在没学习开窗函数之前，我们都知道，用了分组之后，查询字段就只能是分组字段和聚合的字段，这带来了极大的不方便，有时我们查询时需要分组，又需要查询不分组的字段，每次都要又到子查询，这样显得sql语句复杂难懂，给维护代码的人带来很大的痛苦，然而开窗函数出现了，曙光也来临了。如果要想更具体了解开窗函数，请看书《程序员的SQL金典》，开窗函数在mysql不能使用。        开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。它用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用group by语句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。定
在 SQL Server 中使用日历表
hixiaoyang的博客
06-04 989
在 SQL Server 中使用日历表 - 第 1 部分 在 SQL Server 中使用日历表 - 第 2部分
SQL生成整年日期表（全）
qq_654603797
09-29 3088
背景：折线图生成全年时间数据。
SQL如何制作一张日历维度表
SQL数据库开发
08-02 2252
点击上方SQL数据库开发，关注获取SQL视频教程SQL专栏SQL数据库基础知识汇总SQL数据库高级知识汇总问题描述输入想要生成日历的年份，通过调用存储过程，即可生成该年的...
SQL语句生成日历
昕颖的专栏
09-14 946
-- create by zhouxin-- create time 2009-9-14-15DECLARE @Year nvarchar(4)DECLARE @YearMonth nvarchar(7) --月份DECLARE @strTop nvarchar(200)DECLARE @ForI INT,@ForYear INT ,@MaxDay INTDECLARE @
SQL创建日历表
jinma425的专栏
06-25 1847
SELECT DATENAME(yy,GETDATE()) --当前年 SELECT DATENAME(mm,GETDATE()) --当前月 SELECT DATENAME(dd,GETDATE()) --当前号 SELECT DATENAME(week,GETDATE()) --当前年的多少周 SELECT DATENAME (dw, GETDATE()) --星期几 SELECT '第' ...
SQL开窗函数的具体实现详解
01-19
开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计 算的行集组是窗口。在 ISO SQL 规定
某一时段的日期lqs
07-02 183
CREATE TABLE [dbo].[time_dimension] ( [time_id] [int] IDENTITY (1, 1) NOT NULL , [the_date] [datetime] NUL...
Sqlserver生成自然数序列并生成日历的几种方法
weixin_44112373的博客
08-19 619
方法一： SELECT NUMBER FROM MASTER…SPT_VALUES WHERE TYPE=‘P’ and NUMBER BETWEEN 1 AND 100 方法二： WITH CTE_NUMBER AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) NUMBER(n)) SELECT (100T1.n+10T2.n+T3.n)+1 as n FROM CTE_NUMBER T1 CROSS JOIN CTE_NU
教你用SQL生成一张日期维度表
SQL数据库开发
02-09 3510
点击关注“SQL数据库开发”，设为“置顶或星标”，第一时间送达干货问题描述输入想要生成日历的年份，通过调用存储过程，即可生成该年的全部日历。创建表结构我们根据常见的日历表来创建一个含有年，...
数据库开窗函数
孙中明
08-20 1514
SQL日期函数 函数 功能 now() 返回当前的日期和时间 curdate() 返回当前的日期 curtime() 返回当前的时间 date() 提取日期，日期/时间表达式的日期部分 extract() 返回日期/时间的单独部分 date_add() 给日期添加指定的时间间隔 date_sub() 从日期减去指定的时间间隔 datediff() 返回两个日期之间的天数 date_format() 用不同的格式显示日期/时间 day() 取时间字段的天值
sql server 创建日历表
xiaozaq的博客
03-10 3488
sql server 创建日历表： CREATE TABLE [dbo].[Calendar] ( [time_id] [int] IDENTITY (1, 1) NOT NULL primary key, [the_date] [datetime] NULL , [the_day] [nvarchar] (15) NULL , [the_month] [nvar
SQL Server 生成日历表
weixin_30273501的博客
12-22 514
今天做商城订单某时间段的销售趋势图，订单中在该日期范围内可能没有改天的记录，可以使用一个日历表来做关联实现。 CREATE TABLE [dbo].[time_dimension] ( [time_id] [int] IDENTITY (1, 1) NOT NULL , [the_date] [datet...
使用sql语句中的开窗函数查询每个班级中英语成绩最好的同学，结果输出：班级 姓名 分数
最新发布
06-06
在这个查询中，我们使用了开窗函数 `ROW_NUMBER()` 来计算每个班级中英语成绩排名第一的同学。具体来说，我们使用 `PARTITION BY` 按照班级对成绩进行分组，然后使用 `ORDER BY` 对成绩进行降序排列，最后使用 `ROW_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

文盲老顾

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值