本篇内容介绍了“怎么用SQL生成一张带农历的日期维度表”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

创建农历表

CREATETABLESolarData(yearIdintnotnullprimarykey,datachar(7)notnull,dataIntintnotnull)

插入农历数据

农历数据包含从1900年到2049年的具体数据。

INSERTINTOSolarDataSELECT1900,'0x04bd8',19416UNIONALLSELECT1901,'0x04ae0',19168UNIONALLSELECT1902,'0x0a570',42352UNIONALLSELECT1903,'0x054d5',21717UNIONALLSELECT1904,'0x0d260',53856UNIONALLSELECT1905,'0x0d950',55632UNIONALLSELECT1906,'0x16554',91476UNIONALLSELECT1907,'0x056a0',22176UNIONALLSELECT1908,'0x09ad0',39632UNIONALLSELECT1909,'0x055d2',21970UNIONALLSELECT1910,'0x04ae0',19168UNIONALLSELECT1911,'0x0a5b6',42422UNIONALLSELECT1912,'0x0a4d0',42192UNIONALLSELECT1913,'0x0d250',53840UNIONALLSELECT1914,'0x1d255',119381UNIONALLSELECT1915,'0x0b540',46400UNIONALLSELECT1916,'0x0d6a0',54944UNIONALLSELECT1917,'0x0ada2',44450UNIONALLSELECT1918,'0x095b0',38320UNIONALLSELECT1919,'0x14977',84343UNIONALLSELECT1920,'0x04970',18800UNIONALLSELECT1921,'0x0a4b0',42160UNIONALLSELECT1922,'0x0b4b5',46261UNIONALLSELECT1923,'0x06a50',27216UNIONALLSELECT1924,'0x06d40',27968UNIONALLSELECT1925,'0x1ab54',109396UNIONALLSELECT1926,'0x02b60',11104UNIONALLSELECT1927,'0x09570',38256UNIONALLSELECT1928,'0x052f2',21234UNIONALLSELECT1929,'0x04970',18800UNIONALLSELECT1930,'0x06566',25958UNIONALLSELECT1931,'0x0d4a0',54432UNIONALLSELECT1932,'0x0ea50',59984UNIONALLSELECT1933,'0x06e95',28309UNIONALLSELECT1934,'0x05ad0',23248UNIONALLSELECT1935,'0x02b60',11104UNIONALLSELECT1936,'0x186e3',100067UNIONALLSELECT1937,'0x092e0',37600UNIONALLSELECT1938,'0x1c8d7',116951UNIONALLSELECT1939,'0x0c950',51536UNIONALLSELECT1940,'0x0d4a0',54432UNIONALLSELECT1941,'0x1d8a6',120998UNIONALLSELECT1942,'0x0b550',46416UNIONALLSELECT1943,'0x056a0',22176UNIONALLSELECT1944,'0x1a5b4',107956UNIONALLSELECT1945,'0x025d0',9680UNIONALLSELECT1946,'0x092d0',37584UNIONALLSELECT1947,'0x0d2b2',53938UNIONALLSELECT1948,'0x0a950',43344UNIONALLSELECT1949,'0x0b557',46423UNIONALLSELECT1950,'0x06ca0',27808UNIONALLSELECT1951,'0x0b550',46416UNIONALLSELECT1952,'0x15355',86869UNIONALLSELECT1953,'0x04da0',19872UNIONALLSELECT1954,'0x0a5d0',42448UNIONALLSELECT1955,'0x14573',83315UNIONALLSELECT1956,'0x052d0',21200UNIONALLSELECT1957,'0x0a9a8',43432UNIONALLSELECT1958,'0x0e950',59728UNIONALLSELECT1959,'0x06aa0',27296UNIONALLSELECT1960,'0x0aea6',44710UNIONALLSELECT1961,'0x0ab50',43856UNIONALLSELECT1962,'0x04b60',19296UNIONALLSELECT1963,'0x0aae4',43748UNIONALLSELECT1964,'0x0a570',42352UNIONALLSELECT1965,'0x05260',21088UNIONALLSELECT1966,'0x0f263',62051UNIONALLSELECT1967,'0x0d950',55632UNIONALLSELECT1968,'0x05b57',23383UNIONALLSELECT1969,'0x056a0',22176UNIONALLSELECT1970,'0x096d0',38608UNIONALLSELECT1971,'0x04dd5',19925UNIONALLSELECT1972,'0x04ad0',19152UNIONALLSELECT1973,'0x0a4d0',42192UNIONALLSELECT1974,'0x0d4d4',54484UNIONALLSELECT1975,'0x0d250',53840UNIONALLSELECT1976,'0x0d558',54616UNIONALLSELECT1977,'0x0b540',46400UNIONALLSELECT1978,'0x0b5a0',46496UNIONALLSELECT1979,'0x195a6',103846UNIONALLSELECT1980,'0x095b0',38320UNIONALLSELECT1981,'0x049b0',18864UNIONALLSELECT1982,'0x0a974',43380UNIONALLSELECT1983,'0x0a4b0',42160UNIONALLSELECT1984,'0x0b27a',45690UNIONALLSELECT1985,'0x06a50',27216UNIONALLSELECT1986,'0x06d40',27968UNIONALLSELECT1987,'0x0af46',44870UNIONALLSELECT1988,'0x0ab60',43872UNIONALLSELECT1989,'0x09570',38256UNIONALLSELECT1990,'0x04af5',19189UNIONALLSELECT1991,'0x04970',18800UNIONALLSELECT1992,'0x064b0',25776UNIONALLSELECT1993,'0x074a3',29859UNIONALLSELECT1994,'0x0ea50',59984UNIONALLSELECT1995,'0x06b58',27480UNIONALLSELECT1996,'0x055c0',21952UNIONALLSELECT1997,'0x0ab60',43872UNIONALLSELECT1998,'0x096d5',38613UNIONALLSELECT1999,'0x092e0',37600UNIONALLSELECT2000,'0x0c960',51552UNIONALLSELECT2001,'0x0d954',55636UNIONALLSELECT2002,'0x0d4a0',54432UNIONALLSELECT2003,'0x0da50',55888UNIONALLSELECT2004,'0x07552',30034UNIONALLSELECT2005,'0x056a0',22176UNIONALLSELECT2006,'0x0abb7',43959UNIONALLSELECT2007,'0x025d0',9680UNIONALLSELECT2008,'0x092d0',37584UNIONALLSELECT2009,'0x0cab5',51893UNIONALLSELECT2010,'0x0a950',43344UNIONALLSELECT2011,'0x0b4a0',46240UNIONALLSELECT2012,'0x0baa4',47780UNIONALLSELECT2013,'0x0ad50',44368UNIONALLSELECT2014,'0x055d9',21977UNIONALLSELECT2015,'0x04ba0',19360UNIONALLSELECT2016,'0x0a5b0',42416UNIONALLSELECT2017,'0x15176',86390UNIONALLSELECT2018,'0x052b0',21168UNIONALLSELECT2019,'0x0a930',43312UNIONALLSELECT2020,'0x07954',31060UNIONALLSELECT2021,'0x06aa0',27296UNIONALLSELECT2022,'0x0ad50',44368UNIONALLSELECT2023,'0x05b52',23378UNIONALLSELECT2024,'0x04b60',19296UNIONALLSELECT2025,'0x0a6e6',42726UNIONALLSELECT2026,'0x0a4e0',42208UNIONALLSELECT2027,'0x0d260',53856UNIONALLSELECT2028,'0x0ea65',60005UNIONALLSELECT2029,'0x0d530',54576UNIONALLSELECT2030,'0x05aa0',23200UNIONALLSELECT2031,'0x076a3',30371UNIONALLSELECT2032,'0x096d0',38608UNIONALLSELECT2033,'0x04bd7',19415UNIONALLSELECT2034,'0x04ad0',19152UNIONALLSELECT2035,'0x0a4d0',42192UNIONALLSELECT2036,'0x1d0b6',118966UNIONALLSELECT2037,'0x0d250',53840UNIONALLSELECT2038,'0x0d520',54560UNIONALLSELECT2039,'0x0dd45',56645UNIONALLSELECT2040,'0x0b5a0',46496UNIONALLSELECT2041,'0x056d0',22224UNIONALLSELECT2042,'0x055b2',21938UNIONALLSELECT2043,'0x049b0',18864UNIONALLSELECT2044,'0x0a577',42359UNIONALLSELECT2045,'0x0a4b0',42160UNIONALLSELECT2046,'0x0aa50',43600UNIONALLSELECT2047,'0x1b255',111189UNIONALLSELECT2048,'0x06d20',27936UNIONALLSELECT2049,'0x0ada0',44448

(提示：可以左右滑动代码)

计算具体农历

农历的计算有固定的规则，我们通过下面的算法计算出公历日期对应的农历。

注：以下算法来自网络

createfunctiondbo.fn_GetLunar(@solardaydatetime)returnsnvarchar(30)asbegindeclare@soldataintdeclare@offsetintdeclare@ilunarintdeclare@iintdeclare@jintdeclare@ydaysintdeclare@mdaysintdeclare@mleapintdeclare@mleap1intdeclare@mleapnumintdeclare@bleapsmallintdeclare@tempintdeclare@yearnvarchar(10)declare@monthnvarchar(10)declare@daynvarchar(10)declare@chinesenumnvarchar(10)declare@outputdatenvarchar(30)set@offset=datediff(day,'1900-01-30',@solarday)--确定农历年开始set@i=1900--set@offset=@soldatawhile@i<2050and@offset>0beginset@ydays=348set@mleapnum=0select@ilunar=dataintfromsolardatawhereyearid=@i--传回农历年的总天数set@j=32768while@j>8beginif@ilunar&@j>0set@ydays=@ydays+1set@j=@j/2end--传回农历年闰哪个月1-12,没闰传回0set@mleap=@ilunar&15--传回农历年闰月的天数,加在年的总天数上if@mleap>0beginif@ilunar&65536>0set@mleapnum=30elseset@mleapnum=29set@ydays=@ydays+@mleapnumendset@offset=@offset-@ydaysset@i=@i+1endif@offset<=0beginset@offset=@offset+@ydaysset@i=@i-1end--确定农历年结束set@year=@i--确定农历月开始set@i=1select@ilunar=dataintfromsolardatawhereyearid=@year--判断那个月是润月set@mleap=@ilunar&15set@bleap=0while@i<13and@offset>0begin--判断润月set@mdays=0if(@mleap>0and@i=(@mleap+1)and@bleap=0)begin--是润月set@i=@i-1set@bleap=1set@mleap1=@mleap--传回农历年闰月的天数if@ilunar&65536>0set@mdays=30elseset@mdays=29endelse--不是润月beginset@j=1set@temp=65536while@j<=@ibeginset@temp=@temp/2set@j=@j+1endif@ilunar&@temp>0set@mdays=30elseset@mdays=29end--解除润月if@bleap=1and@i=(@mleap+1)set@bleap=0set@offset=@offset-@mdaysset@i=@i+1endif@offset<=0beginset@offset=@offset+@mdaysset@i=@i-1end--确定农历月结束set@month=@i--确定农历日结束set@day=ltrim(@offset)--输出日期set@chinesenum=N'〇一二三四五六七八九十'whilelen(@year)>0select@outputdate=isnull(@outputdate,'')+substring(@chinesenum,left(@year,1)+1,1),@year=stuff(@year,1,1,'')set@outputdate=@outputdate+N'年'+case@mleap1when@monththenN'润'else''endifcast(@monthasint)<10set@outputdate=@outputdate+case@monthwhen1thenN'正'elsesubstring(@chinesenum,left(@month,1)+1,1)endelseifcast(@monthasint)>=10set@outputdate=@outputdate+case@monthwhen'10'thenN'十'when11thenN'十一'elseN'十二'endset@outputdate=@outputdate+N'月'ifcast(@dayasint)<10set@outputdate=@outputdate+N'初'+substring(@chinesenum,left(@day,1)+1,1)elseif@daybetween'10'and'19'set@outputdate=@outputdate+case@daywhen'10'thenN'初十'elseN'十'+substring(@chinesenum,right(@day,1)+1,1)endelseif@daybetween'20'and'29'set@outputdate=@outputdate+case@daywhen'20'thenN'二十'elseN'廿'end+case@daywhen'20'thenN''elsesubstring(@chinesenum,right(@day,1)+1,1)endelseset@outputdate=@outputdate+N'三十'return@outputdateendGO

测试农历算法

我们创建的是一个农历的函数，当我们将日期传递给这个函数，其就会返回具体的日历了。

SELECTdbo.fn_GetLunar('2021-02-19')Lunar

查看一下返回的结果：

我们去查了一下日历，验证结果是正确的

将农历加入到日历表

已经获得了公历转换成农历的转换函数，我们直接调用该函数即可。修改一下日历表结构(在表CALENDAR_INFO 中添加一个字符串格式的字段Lunar)和存储过程。更新后的存储过程如下：

CREATEPROCPROC_CALENDAR(@YEARINT)ASBEGINDECLARE@iINT;DECLARE@START_DATEVARCHAR(20);DECLARE@END_DATEVARCHAR(20);DECLARE@DATE_COUNTINT;SET@i=0;--定义一年的开始日期，用CONCAT函数将年份和月份日期拼接起来SET@START_DATE=CONCAT(@YEAR,'-01-01');--定义一年的结束日期SET@END_DATE=CONCAT(@YEAR+1,'-01-01');--如果表已经存在要新建的日历，则先删除DELETEFROMCALENDAR_INFOWHEREYEAR=@YEAR;--计算开始到结束日期一共有多少天，DATEDIFF函数计算日期的间隔天数SET@DATE_COUNT=DATEDIFF(DAY,@START_DATE,@END_DATE);--建立循环，条件是@i小于一年的天数WHILE@i<@DATE_COUNTBEGININSERTINTOCALENDAR_INFO(COUNTRY,CDR_CODE,DATE_NAME,LUNAR,YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST)SELECT'CN','CN01',--CONVERT函数将@START_DATE转换成指定的格式CONVERT(CHAR(10),@START_DATE,111)DATE_NAME,--将公历日期转换为农历dbo.fn_GetLunar(CONVERT(datetime,@start_date,101))LUNAR,--DATENAME获取@START_DATE的年份CONVERT(CHAR(4),DATENAME(YEAR,@START_DATE))YEAR,--DATENAME获取@START_DATE的月份CONVERT(CHAR(2),DATENAME(MONTH,@START_DATE))MONTH,--DATENAME获取@START_DATE的星期CONVERT(CHAR(6),DATENAME(WEEKDAY,@START_DATE))WEEK,--DATENAME获取@START_DATE的日期CONVERT(CHAR(2),DATENAME(DAY,@START_DATE))DAY,CONVERT(CHAR(10),@START_DATE,120)CDR_DATE,'平凡世界',GETDATE()CREATE_DATE,'Lyven'--插入完成后，对@i进行自加1处理SET@i=@i+1;--更新@START_DATE的值，在原来的基础上使用DATEADD函数增加一天SET@START_DATE=CONVERT(CHAR(10),DATEADD(DAY,1,@START_DATE),120);ENDEND

执行存储过程

EXECproc_calendar2019

结果如下：

我们去查了一下日历，验证结果是正确的

日期维度表作用

可能有同学会问，花这么大力气就写了个这个，到底有什么用啊?

既然叫维度表，那肯定是跟维度有关了，有了这个维度表，我们可以通过多维数据集来查看不同日期维度的具体数据，特别是应用在可视化报表开发方面。

下面就是一个比较简单的Power BI报表，这里我们就使用到了日期维度表中的年月。

Power BI效果图

至此，一个包含农历的完整日期维度表就生成了，有兴趣的小伙伴可以用MySQL或Oracle进行改写一下。

“怎么用SQL生成一张带农历的日期维度表”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！