复杂年月处理.sql --定义基本数字表 declare@T1table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime) insertinto@T1 select12,'单位1','2003/04/01','2004/05/01' unionallselect22,'单位2','2001/02/01','2003/02/01' unionallselect42,'单位3','2000/04/01','2003/05/01' unionallselect25,'单位5','2003/04/01','2003/05/01' --定义年表 declare@NBtable(代码 int,名称 varchar(10),年份 int) insertinto@NB select12,'单位1',2003 unionallselect12,'单位1',2004 unionallselect22,'单位2',2001 unionallselect22,'单位2',2002 unionallselect22,'单位2',2003 --定义月表 declare@YBtable(代码 int,名称 varchar(10),年份 int,月份 varchar(2)) insertinto@YB select12,'单位1',2003,'04' unionallselect22,'单位2',2001,'01' unionallselect22,'单位2',2001,'12' --为年表+月表数据处理准备临时表 selecttop8246 y=identity(int,1753,1) into #tby from (select id from syscolumns) a, (select id from syscolumns) b, (select id from syscolumns) c --为月表数据处理准备临时表 selecttop12 m=identity(int,1,1) into #tbm from syscolumns /**//*--数据处理--*/ --年表数据处理 select a.* from( select a.代码,a.名称,年份=b.y from@T1 a,#tby b where b.y betweenyear(参加时间) andyear(终止时间) ) a leftjoin@NB b on a.代码=b.代码 and a.年份=b.年份 where b.代码 isnull --月表数据处理 select a.* from( select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m asvarchar),2) from@T1 a,#tby b,#tbm c where b.y*100+c.m betweenconvert(varchar(6),参加时间,112) andconvert(varchar(6),终止时间,112) ) a leftjoin@YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份 where b.代码 isnull orderby a.代码,a.名称,a.年份,a.月份 --删除数据处理临时表 droptable #tby,#tbm
任意两个时间之间的星期几的次数-横.sql ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_weekdaycount] GO /**//*--计算任意两个时间之间的星期几的次数(横向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 --邹建 2004.08(引用请保留此信息)--*/ /**//*--调用示例 select * from f_weekdaycount('2004-9-01','2004-9-02') --*/ createfunction f_weekdaycount( @dt_begindatetime, @dt_enddatetime )returnstable as return( select 跨周数 ,周一=case a when-1thencasewhen1between b and c then1else0end when0thencasewhen b<=1then1else0end +casewhen c>=1then1else0end else a+casewhen b<=1then1else0end +casewhen c>=1then1else0end end ,周二=case a when-1thencasewhen2between b and c then1else0end when0thencasewhen b<=2then1else0end +casewhen c>=2then1else0end else a+casewhen b<=2then1else0end +casewhen c>=2then1else0end end ,周三=case a when-1thencasewhen3between b and c then1else0end when0thencasewhen b<=3then1else0end +casewhen c>=3then1else0end else a+casewhen b<=3then1else0end +casewhen c>=3then1else0end end ,周四=case a when-1thencasewhen4between b and c then1else0end when0thencasewhen b<=4then1else0end +casewhen c>=4then1else0end else a+casewhen b<=4then1else0end +casewhen c>=4then1else0end end ,周五=case a when-1thencasewhen5between b and c then1else0end when0thencasewhen b<=5then1else0end +casewhen c>=5then1else0end else a+casewhen b<=5then1else0end +casewhen c>=5then1else0end end ,周六=case a when-1thencasewhen6between b and c then1else0end when0thencasewhen b<=6then1else0end +casewhen c>=6then1else0end else a+casewhen b<=6then1else0end +casewhen c>=6then1else0end end ,周日=case a when-1thencasewhen0between b and c then1else0end when0thencasewhen b<=0then1else0end +casewhen c>=0then1else0end else a+casewhen b<=0then1else0end +casewhen c>=0then1else0end end from( select 跨周数=casewhen@dt_begin<@dt_end then (datediff(day,@dt_begin,@dt_end)+7)/7 else (datediff(day,@dt_end,@dt_begin)+7)/7end ,a=casewhen@dt_begin<@dt_end thendatediff(week,@dt_begin,@dt_end)-1 elsedatediff(week,@dt_end,@dt_begin)-1end ,b=casewhen@dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 else (@@datefirst+datepart(weekday,@dt_end)-1)%7end ,c=casewhen@dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_end)-1)%7 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7end)a ) go
统计--交叉表+日期+优先.sql
统计--交叉表+日期+优先.sql --交叉表,根据优先级取数据,日期处理 createtable tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int) insert tb select1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1 unionallselect1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1 unionallselect1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1 unionallselect1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 --union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2 unionallselect1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2 unionallselect1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1 unionallselect1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1 unionallselect1,'A2','未订','10:00','11:00',1 ,5 ,null ,null ,1 --union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 unionallselect1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2 --union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2 go /**//*--楼主这个问题要考虑几个方面 1. 取星期时,set datefirst 的影响 2. 优先级问题 3. qid,rid 应该是未知的(动态变化的) --*/ --实现的存储过程如下 createproc p_qry @datesmalldatetime--要查询的日期 as set nocount on declare@weekint,@snvarchar(4000) --格式化日期和得到星期 select@date=convert(char(10),@date,120) ,@week=(@@datefirst+datepart(weekday,@date)-1)%7 ,@s='' select id=identity(int),*into #t from( selecttop100percent qid,rid,tagname, starttime=convert(char(5),starttime,108), endtime=convert(char(5),endtime,108) from tb where (@weekbetween startweekday and endweekday) or(@datebetween startdate and enddate) orderby qid,rid,starttime,d desc)a select@s=@s+N',['+rtrim(rid) +N']=max(case when qid='+rtrim(qid) +N' and rid=N'''+rtrim(rid) +N''' then tagname else N'''' end)' from #t groupby qid,rid exec(' select starttime,endtime'+@s+' from #t a where not exists( select * from #t where qid=a.qid and rid=a.rid and starttime=a.starttime and endtime=a.endtime and id<a.id) group by starttime,endtime') go --调用 exec p_qry '2005-1-17' exec p_qry '2005-1-18' go --删除测试 droptable tb dropproc p_qry /**//*--测试结果 starttime endtime A1 A2 --------- ------- ---------- ---------- 08:00 09:00 未订 未订 09:00 10:00 未订 未订 10:00 11:00 未订 未订 starttime endtime A1 A2 --------- ------- ---------- ---------- 08:00 09:00 装修 未订 09:00 10:00 未订 装修 10:00 11:00 装修 未订 --*/
各种字符串分拆处理函数.sql
各种字符串分拆处理函数.sql --各种字符串分函数 ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_splitSTR] GO --3.2.1 循环截取法 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(10) --数据分隔符 )RETURNS@reTABLE(col varchar(100)) AS BEGIN DECLARE@splitlenint SET@splitlen=LEN(@split+'a')-2 WHILECHARINDEX(@split,@s)>0 BEGIN INSERT@reVALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET@s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT@reVALUES(@s) RETURN END GO /**//*==============================================*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_splitSTR] GO --3.2.3.1 使用临时性分拆辅助表法 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(10) --数据分隔符 )RETURNS@reTABLE(col varchar(100)) AS BEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE@tTABLE(ID intIDENTITY,b bit) INSERT@t(b) SELECTTOP80000FROM syscolumns a,syscolumns b INSERT@reSELECTSUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) FROM@t WHERE ID<=LEN(@s+'a') ANDCHARINDEX(@split,@split+@s,ID)=ID RETURN END GO /**//*==============================================*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_splitSTR] GO ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[tb_splitSTR]') andobjectproperty(id,N'IsUserTable')=1) droptable[dbo].[tb_splitSTR] GO --3.2.3.2 使用永久性分拆辅助表法 --字符串分拆辅助表 SELECTTOP8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR FROM syscolumns a,syscolumns b GO --字符串分拆处理函数 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(10) --数据分隔符 )RETURNSTABLE AS RETURN( SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) asvarchar(100)) FROM tb_splitSTR WHERE ID<=LEN(@s+'a') ANDCHARINDEX(@split,@split+@s,ID)=ID) GO /**//*==============================================*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_splitSTR] GO --3.2.5 将数据项按数字与非数字再次拆份 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(10) --数据分隔符 )RETURNS@reTABLE(No varchar(100),Value varchar(20)) AS BEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE@tTABLE(ID intIDENTITY,b bit) INSERT@t(b) SELECTTOP80000FROM syscolumns a,syscolumns b INSERT@re SELECT No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')), Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1)) FROM( SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)) FROM@t WHERE ID<=LEN(@s+'a') ANDCHARINDEX(@split,@split+@s,ID)=ID)a RETURN END GO /**//*==============================================*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_splitSTR] GO --3.2.6 分拆短信数据 CREATEFUNCTION f_splitSTR(@svarchar(8000)) RETURNS@reTABLE(split varchar(10),value varchar(100)) AS BEGIN DECLARE@splitsTABLE(split varchar(10),splitlen asLEN(split)) INSERT@splits(split) SELECT'AC'UNIONALL SELECT'BC'UNIONALL SELECT'CC'UNIONALL SELECT'DC' DECLARE@pos1int,@pos2int,@splitvarchar(10),@splitlenint SELECTTOP1 @pos1=1,@split=split,@splitlen=splitlen FROM@splits WHERE@sLIKE split+'%' WHILE@pos1>0 BEGIN SELECTTOP1 @pos2=CHARINDEX(split,@s,@splitlen+1) FROM@splits WHERECHARINDEX(split,@s,@splitlen+1)>0 ORDERBYCHARINDEX(split,@s,@splitlen+1) IF@@ROWCOUNT=0 BEGIN INSERT@reVALUES(@split,STUFF(@s,1,@splitlen,'')) RETURN END ELSE BEGIN INSERT@reVALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1)) SELECTTOP1 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'') FROM@splits WHERESTUFF(@s,1,@pos2-1,'') LIKE split+'%' END END RETURN END GO
各种字符串合并处理示例.sql
各种字符串合并处理示例.sql --各种字符串分函数 --3.3.1 使用游标法进行字符串合并处理的示例。 --处理的数据 CREATETABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT'a',1 UNIONALLSELECT'a',2 UNIONALLSELECT'b',1 UNIONALLSELECT'b',2 UNIONALLSELECT'b',3 --合并处理 --定义结果集表变量 DECLARE@tTABLE(col1 varchar(10),col2 varchar(100)) --定义游标并进行合并处理 DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDERBY col1,col2 DECLARE@col1_oldvarchar(10),@col1varchar(10),@col2int,@svarchar(100) OPEN tb FETCH tb INTO@col1,@col2 SELECT@col1_old=@col1,@s='' WHILE@@FETCH_STATUS=0 BEGIN IF@col1=@col1_old SELECT@s=@s+','+CAST(@col2asvarchar) ELSE BEGIN INSERT@tVALUES(@col1_old,STUFF(@s,1,1,'')) SELECT@s=','+CAST(@col2asvarchar),@col1_old=@col1 END FETCH tb INTO@col1,@col2 END INSERT@tVALUES(@col1_old,STUFF(@s,1,1,'')) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT*FROM@t DROPTABLE tb /**//*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ GO /**//*==============================================*/ --3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例 --处理的数据 CREATETABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT'a',1 UNIONALLSELECT'a',2 UNIONALLSELECT'b',1 UNIONALLSELECT'b',2 UNIONALLSELECT'b',3 GO --合并处理函数 CREATEFUNCTION dbo.f_str(@col1varchar(10)) RETURNSvarchar(100) AS BEGIN DECLARE@revarchar(100) SET@re='' SELECT@re=@re+','+CAST(col2 asvarchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,'')) END GO --调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUPBY col1 --删除测试 DROPTABLE tb DROPFUNCTION f_str /**//*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ GO /**//*==============================================*/ --3.3.3 使用临时表实现字符串合并处理的示例 --处理的数据 CREATETABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT'a',1 UNIONALLSELECT'a',2 UNIONALLSELECT'b',1 UNIONALLSELECT'b',2 UNIONALLSELECT'b',3 --合并处理 SELECT col1,col2=CAST(col2 asvarchar(100)) INTO #t FROM tb ORDERBY col1,col2 DECLARE@col1varchar(10),@col2varchar(100) UPDATE #t SET @col2=CASEWHEN@col1=col1 THEN@col2+','+col2 ELSE col2 END, @col1=col1, col2=@col2 SELECT*FROM #t /**//*--更新处理后的临时表 col1 col2 ---------- ------------- a 1 a 1,2 b 1 b 1,2 b 1,2,3 --*/ --得到最终结果 SELECT col1,col2=MAX(col2) FROM #t GROUPBY col1 /**//*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ --删除测试 DROPTABLE tb,#t GO /**//*==============================================*/ --3.3.4.1 每组 <=2 条记录的合并 --处理的数据 CREATETABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT'a',1 UNIONALLSELECT'a',2 UNIONALLSELECT'b',1 UNIONALLSELECT'b',2 UNIONALLSELECT'c',3 --合并处理 SELECT col1, col2=CAST(MIN(col2) asvarchar) +CASE WHENCOUNT(*)=1THEN'' ELSE','+CAST(MAX(col2) asvarchar) END FROM tb GROUPBY col1 DROPTABLE tb /**//*--结果 col1 col2 ---------- ---------- a 1,2 b 1,2 c 3 --*/ --3.3.4.2 每组 <=3 条记录的合并 --处理的数据 CREATETABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT'a',1 UNIONALLSELECT'a',2 UNIONALLSELECT'b',1 UNIONALLSELECT'b',2 UNIONALLSELECT'b',3 UNIONALLSELECT'c',3 --合并处理 SELECT col1, col2=CAST(MIN(col2) asvarchar) +CASE WHENCOUNT(*)=3THEN',' +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOTIN(MAX(a.col2),MIN(a.col2))) asvarchar) ELSE'' END +CASE WHENCOUNT(*)>=2THEN','+CAST(MAX(col2) asvarchar) ELSE'' END FROM tb a GROUPBY col1 DROPTABLE tb /**//*--结果 col1 col2 ---------- ------------ a 1,2 b 1,2,3 c 3 --*/ GO