构建显示全年日历的SQL

要求:

实现全年按月显示日期的样式

分析:

上面的列:月份 然后按 [星期] 日一二三四五六这样 排列

每行代表着什么,相同的周数。

我们从从一年里的所有date,选出符合月份、周序、星期和日期,这样才会一一对应。从date里我们可以算出一天的其它属性,比如月份、一年内的第几周、在月里的日期、在一周里的序号(周序)等,也就可以衍生出另一张表来。这张表包含字段有:日期、月份、一年内的第几周、在月里的日期、在一周里的序号。

有了这张表,我们就可以通过上面学到的行列转换,实现上述目标了。

我们可能通过以下步骤来实现:

1. 创建只有一列的表year_d ,字段为date1 date.

包括全年的天值,从11日到1231日,共365行。

2.创建一张衍生表 year_a 字段为

Date1 date, month number,weeks number,dates number,weekd number

分别对就日期,月份,周序,日序(在一月里的),周几

3.根据上面这张表行列转换,以月份和周序分组和排序,以周几为点,对日序进行转换。

 

步骤:

/**** 创建一个表

1.       求出全年有多少天

Select add_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year') from dual; 365

2.       创建有一年的所有日期的表

Create table year_d as

select trunc(sysdate,'y')-1 +rownum date1

              from all_objects

             where rownum <=365 365就是上面求出的数字,一年里的天数

说明:从all_objects表里,取出rownum,类似于取1-365这些数字,减1后,从1加起,就实现了得到全年的所有的日期了。

 

3.       创建一张包含日期、月份、一年内的第几周、在月里的日期、在一周里的序号

格式:月mm,周 iw,日期序号 dd 周号d

create table year_a as

select date1,

to_number(to_char(date1,'mm')) months,

to_number(to_char( date1+1, 'iw' )) weeks,

to_number(to_char(date1,'dd')) dates ,

to_number(to_char(date1,'d')) weekd

from year_d;

 

select * from year_a;

 

4.       实现转换:

select     months,

         weeks,

              max(decode(weekd,'1',dates))"",

              max(decode(weekd,'2',dates)) "",

              max(decode(weekd,'3',dates)) "",

              max(decode(weekd,'4',dates)) "",

              max(decode(weekd,'5',dates)) "",

              max(decode(weekd,'6',dates)) "",

            max(decode(weekd,'7',dates)) ""

     from year_a group by months,weeks

order by months,weeks;

/****

select * from

(select months,weeks,weekd,dates from year_a)

  pivot

(sum(dates) for weekd in (1 as "" ,2 as "" ,3 as "",4 as "",5 as "",6 as "",7 as ""))

Order by months,weeks

/

****/

以上所有的语句,实际上只用到了从sysdate里取本年的第一天,借用了all_objects表取得rownum的序列。当然可以用其他的表,只要行数多于366就行了。

有了以上的基础,咱们就可以把上面的那些语句整合起来,而不需要中间表了。再格式化一下,就可以相当美观了。

 

语句成品:

break on month skip 1

set linesize 120

column month format a20

select lpad( Month, 20-(20-length(month))/2 ) month,

          "", "", "", "", "", "", ""

      from (

    select to_char(dt,'fmMonthfm') month,

          to_char(dt+1,'iw') week,

           max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "",

           max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "",

           max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "",

           max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "",

          max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "",

          max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "",

          max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) ""

     from ( select trunc(sysdate,'y')-1+rownum dt

              from all_objects

             where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')

   )

   group by to_char(dt,'fmMonthfm'), to_char( dt+1, 'iw' )

          )

    order by to_date( month, 'Month' ), to_number(week)

   /

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10804699/viewspace-712709/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/10804699/viewspace-712709/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 以下是 SQL Server 生成全年日历的示例代码: ``` DECLARE @StartDate DATE = '2022-01-01'; DECLARE @EndDate DATE = '2022-12-31'; WITH dates AS ( SELECT @StartDate AS date UNION ALL SELECT DATEADD(day, 1, date) FROM dates WHERE DATEADD(day, 1, date) <= @EndDate ) SELECT DATEPART(year, date) AS [Year], DATEPART(month, date) AS [Month], DATEPART(day, date) AS [Day], DATENAME(weekday, date) AS [Weekday] FROM dates OPTION (MAXRECURSION 0); ``` 在上面的代码中,我们首先定义了开始和结束日期,然后使用递归CTE生成从开始日期到结束日期的所有日期。最后,我们选择每个日期的年份、月份、日份和星期几,并使用DATENAME函数将星期几转换为文本。选项(MAXRECURSION 0)指定在生成日期时不限制递归的次数。 执以上代码,你将得到一个包含所有日期的结果集,其中包括每个日期的年份、月份、日份和星期几。 ### 回答2: 在SQL Server中生成全年日历可以通过以下步骤实现: 1. 创建一个日期表:首先,我们需要创建一个日期表,用来存储全年日期信息。这个表可以包含日期、星期、月份等相关字段。可以使用日期函数来生成这个表的数据,例如使用`DATEADD`函数来增加日期,`DATENAME`函数来获取星期和月份的名称。 2. 插入数据:使用`INSERT INTO`语句将生成的日期数据插入到日期表中。可以使用循环或递归的方式逐插入数据。在插入数据之前,可以使用`TRUNCATE TABLE`语句清空原有的日期表,以免出现重复数据。 3. 定义日期范围:根据需要生成的日历范围,可以使用`DECLARE`语句定义起始日期和结束日期的变量。例如:`DECLARE @StartDate DATE = '2022-01-01', @EndDate DATE = '2022-12-31'`。 4. 生成日历:使用`SELECT`语句查询日期表,并使用`WHERE`子句过滤出在日期范围内的数据。可以按照一周七天或一个月的方式进分组,并使用`ORDER BY`子句按照日期的顺序排序。 下面是一个示例的SQL查询,用来生成2022年度的日历: ``` DECLARE @StartDate DATE = '2022-01-01', @EndDate DATE = '2022-12-31' CREATE TABLE #Calendar ( DateColumn DATE, WeekdayColumn VARCHAR(20), MonthColumn VARCHAR(20) ) WHILE @StartDate <= @EndDate BEGIN INSERT INTO #Calendar (DateColumn, WeekdayColumn, MonthColumn) VALUES (@StartDate, DATENAME(WEEKDAY, @StartDate), DATENAME(MONTH, @StartDate)) SET @StartDate = DATEADD(DAY, 1, @StartDate) END SELECT WeekdayColumn, DateColumn, MonthColumn FROM #Calendar WHERE DateColumn BETWEEN @StartDate AND @EndDate ORDER BY DateColumn ``` 以上就是使用SQL Server生成全年日历的一种方法。可以根据自己的需要进调整和扩展,来满足不同的日历需求。 ### 回答3: 要在SQL Server中生成全年日历,可以使用递归查询和日期函数来实现。以下是一种实现方法: 首先,创建一个表来存储所有的日期和相关的信息,包括年份、月份日期、星期、是否为工作日等等。 ```sql CREATE TABLE Calendar ( Year INT, Month INT, Day INT, Weekday INT, IsWorkingDay BIT ); ``` 接下来,使用递归查询来生成所有的日期。递归查询就是在查询的过程中调用自己来进迭代操作。 ```sql WITH RecursiveCalendar AS ( -- 基础查询,生成第一个日期 SELECT DATEPART(YEAR, GETDATE()) AS Year, DATEPART(MONTH, GETDATE()) AS Month, DATEPART(DAY, GETDATE()) AS Day, DATEPART(WEEKDAY, GETDATE()) AS Weekday, CASE WHEN DATEPART(WEEKDAY, GETDATE()) NOT IN (1, 7) THEN 1 ELSE 0 END AS IsWorkingDay UNION ALL -- 递归查询,生成后续日期 SELECT Year, Month, Day+1, (Weekday+1) % 7, CASE WHEN (Weekday+1) % 7 NOT IN (1, 7) THEN 1 ELSE 0 END FROM RecursiveCalendar WHERE Day+1 <= DATEPART(DAY, DATEFROMPARTS(Year, Month+1, 1)) ) -- 将结果插入到Calendar表中 INSERT INTO Calendar (Year, Month, Day, Weekday, IsWorkingDay) SELECT Year, Month, Day, Weekday, IsWorkingDay FROM RecursiveCalendar OPTION (MAXRECURSION 366); -- 设置递归最大次数为366,确保处理闰年 ``` 最后,你就可以使用以下查询来获取全年日历数据: ```sql SELECT * FROM Calendar; ``` 以上就是在SQL Server中生成全年日历的方法。通过使用递归查询和日期函数,我们可以轻松地生成任何年份的日历数据。请注意,上述示例仅提供了基础实现,你可以根据自己的需求对其进修改和优化。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值