存储过程自动生成显示 2016年1月1日 ~ 2025年12月31日 ）
1、问题： 在Q群中一位小伙伴问题，然后当时就给出了一个简单的想法，今天就写了一个存储过程实现它，从最简单的字段一步步实现的
Date_Key这列还需要这样转换一下，懒得转换了 Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
2、首先，就是想用循环将 日期值（如： 2016-01-01 ~ 2025-12-31，季度值， 这2个字段显示出来），因为他们是主要的字段，其他转换一下即可
写基本框框结构，然后再一段一段拼接上去
3、“表结构”（泛指，表、 存储过程）
表结构（只看 t_YM_Week_Quarter 这一个就可）：
/* 自动生成年月日、季度，表结构 */ USE [testdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO --Drop table t_YM_Week_Quarter GO CREATE TABLE [dbo].[t_YM_Week_Quarter]( --------------作用于此表 [cYMD] datetime NOT NULL, [cYear] [char](4) NOT NULL, [cMonth] [char](2) NOT NULL, [cYYYYMM] [char](6) NOT NULL, [iWeekNo] [int] NOT NULL, [iQuarterNo] [int] NOT NULL, [cYMDmap] datetime NOT NULL, ) ON [PRIMARY] GO select * from t_YM_Week_Quarter CREATE TABLE [dbo].[t_Y]( [cYear] [char](4) NOT NULL, ) ON [PRIMARY] GO --drop table t_YMD GO CREATE TABLE [dbo].[t_YMD]( [cYMD] datetime NOT NULL, ) ON [PRIMARY] GO ------------------------ --drop table t_YMD_Quarter GO CREATE TABLE [dbo].[t_YMD_Quarter]( [cYMD] datetime NOT NULL, [cQuarter] int NOT NULL, ) ON [PRIMARY] GO ------------------------ --drop table t_YMD_Quarter GO CREATE TABLE [dbo].[t_YMD_Quarter]( [cYMD] datetime NOT NULL, [cQuarter] int NOT NULL, ) ON [PRIMARY] GO
--下面更新了之前试用了固定变量的部分
DROP PROC usp_YMD_Quarter GO CREATE PROC [dbo].[usp_YMD_Quarter] @sdate DATETIME, @edate DATETIME AS BEGIN --SET @sdate = '2016-1-1' --SET @edate = '2025-12-31' --DECLARE @iYMD INT DECLARE @iDay INT set @iDay = 0 --DECLARE @iYear INT DECLARE @cYMD datetime DECLARE @TotalDays int DECLARE @cQuarter int DECLARE @iWeekNo int DECLARE @cYear char(4) DECLARE @cMonth char(2) DECLARE @cYYYYMM char(6) DECLARE @cYMDmap char(8) set @TotalDays = DATEDIFF(day,@sdate,@edate) --修改了此处，当时为了调试直接用了固定数据 WHILE @iDay <= CAST(@TotalDays as int) --iDay只是起循环作用，循环到'2016-1-1','2025-12-31'的差 = 3652就停止 BEGIN SET @cYMD=CONVERT(varchar(32),DATEADD(DD,@iDay,@sdate),112) --select DATEADD(DD,1,'2016-1-1') SET @cYear = CAST(datepart(yyyy,@cYMD) as CHAR(4)) --select CAST(datepart(yyyy,'2016-01-01') as CHAR(4)) --SET @cMonth = CAST(datepart(mm,@cYMD) as CHAR(2)) SET @cMonth = CAST(Right(100+Month(datepart(mm,@cYMD)),2) as CHAR(2)) --这样可显示2位的月份，不足2位时前面补0 SET @cYYYYMM = CAST(datepart(yyyy,@cYMD) as CHAR(4))+CAST(Right(100+Month(datepart(mm,@cYMD)),2) as CHAR(2)) --正确 SET @iWeekNo = datepart(ww,@cYMD) SET @cQuarter = datepart(qq,@cYMD) --select datepart(qq,'2016-7-1') SET @cYMDmap = CONVERT(varchar(32),@cYMD,112) --select CONVERT(varchar(32),'2016-01-01',112) --SET @cYMDmap = @cYMD INSERT INTO dbo.t_YM_Week_Quarter --,@cYMDmap (cYMD,cYear,cMonth,cYYYYMM,iWeekNo,iQuarterNo,cYMDmap) --,@cYMDmap VALUES (@cYMD,@cYear,@cMonth,@cYYYYMM,@iWeekNo,@cQuarter,@cYMDmap) SET @iDay=@iDay + 1 END END /* --delete from t_YM_Week_Quarter --convert(varchar(32),cYMD,23) as cYMD,cQuarter exec usp_YMD_Quarter '2016-1-1', '2016-1-20' select convert(varchar(32),cYMD,23) as cYMD,iQuarterNo from t_YM_Week_Quarter order by cYMD SELECT CONVERT(VARCHAR(32), cYMD, 23) as cYMD, cYear, cMonth, cYYYYMM, iWeekNo, iQuarterNo, CONVERT(VARCHAR(32), cYMDmap, 112) as cYMDmap FROM t_YM_Week_Quarter ORDER BY cYMD */ GO
显示结果（截取了首位的部分）：
......
---完成
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
下面是思考部分，可以不用理会它（就是先在旁边写、拼接语句），最后再组合在一起
SELECT Datediff(dd, CONVERT(VARCHAR(7), Getdate(), 121) + '-01', CONVERT (VARCHAR(10), Dateadd(day, -1, Cast(CONVERT(VARCHAR(7), Dateadd(month, 1, Getdate()), 121) + '-01' AS DATETIME)), 121)) AS 天数 sp_helptext Getmonths CREATE FUNCTION Getmonths (@Month SMALLINT, @Year INT) returns SMALLINT /*获取当月的天数*/ AS BEGIN RETURN( Day(Dateadd(dd, -1, Dateadd(m, 1, Cast(@Year AS VARCHAR(4)) + '-' + Cast(@Month AS VARCHAR(2)) + '-01'))) ) END select dbo.Getmonths('5','2020') SELECT Datepart(yyyy, OrderDate) AS OrderYear, Datepart(mm, OrderDate) AS OrderMonth, Datepart(dd, OrderDate) AS OrderDay FROM Orders WHERE OrderId = 1 Select REPLACE(CONVERT(varchar(100), GETDATE(), 112)+CONVERT(varchar(100), GETDATE(), 8),':','') select * from orders select CONVERT(varchar(32),OrderDate,112) from Orders ----------------------------------------------------- Select Right(100+Month(GetDate()),2) select Right(100+Month(datepart(MM,'2016-01-01')),2) select CAST(Right(100+Month(datepart(MM,'2016-01-01')),2) as CHAR(2)) as MM Select CAST(datepart(yyyy,'2016-01-01') as CHAR(4)) as YYYY, CAST(Right(100+Month(datepart(MM,'2016-01-01')),2) as CHAR(2)) as MM, CAST(datepart(yyyy,'2016-01-01') as CHAR(4)) + CAST(Right(100+Month(datepart(MM,'2016-01-01')),2) as CHAR(2)) as YYYYMM
-----------------------------------------------------------------------------
declare @TotalDays
set @TotalDays = select DATEDIFF(day,'2016-1-1','2025-12-31')
select @TotalDays as TotalD
--drop proc usp_Y
GO
--drop PROC usp_Y
CREATE PROC usp_Y
@cYear CHAR(4)
as
begin
DECLARE @iYear int
DECLARE @iDay int
SET @iYear=2016
WHILE @iYear<2026
BEGIN
SET @cYear=Cast(@iYear AS CHAR(4))
INSERT INTO dbo.t_Y
(
cYear )
VALUES (@cYear)
SET @iYear=@iYear + 1
END
END
/*
exec usp_Y 2016
select * from t_Y
*/
-------------------------------------------------------------------------------------------------------------------------------
任务虽然完成，但还是感觉好 Low，看看是否还有其他更简便的方法生成，想结果应该还是有的，......
-------------------------------------------------------------------------------------------------------------------------------
逐渐注意到这类应用（CTE --- Common Table Expressions，从2005就开始了，2000不知道是否有）：
Expert T-SQL Window Functions in SQL Server 2019，Chapter 4: Calculating Running and Moving Aggregates
The query was very easy to write and runs pretty fast. It ran in less than 100
milliseconds on my Azure virtual machine against 100,000 rows
