设置内存选项:
SQLcode
--设置minservermemory配置项EXECsp_configureN'minservermemory(MB)',0
--设置maxservermemory配置项EXECsp_configureN'maxservermemory(MB)',256
--使更新生效RECONFIGUREWITHOVERRIDE
使用文件及文件组.sql:
SQLcode
/*--功能说明
下面的代码是在SQLServer2000上创建名为MyDB的数据库该数据库包括1个主要数据文件,3个用户定义的文件组和1个日志文件ALTERDATABASE语句将用户定义文件组指定为默认文件组.之后,通过指默认的文件组来创建表,并且将图像数据和索引放到指定的文件组中.最后,将文件组中的指定数据文件删除--*/
--切换到master数据库USEmasterGO
--A.创建数据库MyDBCREATEDATABASEMyDB
ONPRIMARY
--主文件组和主要数据文件(NAME='MyDB_Primary',FILENAME='c:\MyDB_Prm.mdf'),
FILEGROUPMyDB_FG1
--用户定义文件组1
(NAME='MyDB_FG1_Dat1',FILENAME='c:\MyDB_FG1_1.ndf'),--次要数据文件1
(NAME='MyDB_FG1_Dat2',FILENAME='d:\MyDB_FG1_2.ndf'),FILEGROUPMyDB_FG2--次要数据文件2--用户定义文件组2
(NAME='MyDB_FG1_Dat',FILENAME='e:\MyDB_FG2.ndf')LOGON(NAME='MyDB_log',FILENAME='d:\MyDB.ldf')GO--次要数据文件--日志文件
--B.修改默认数据文件组ALTERDATABASEMyDBMODIFYFILEGROUPMyDB_FG1DEFAULTGO
--切换到新建的数据库MyDBUSEMyDB
--C.在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2CREATETABLEMyTable(colaintcolbcolcPRIMARYKEY,char(8),8image)
TEXTIMAGE_ONMyDB_FG2
--在用户定义文件组MyDB_FG2上创建索引CREATEINDEXIX_MyTableONMyTable(cola)ONMyDB_FG2GO
--D.将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中,并且清空数据文件MyDB_FG1_Dat1DBCCSHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)--删除数据文件MyDB_FG1_Dat1ALTERDATABASEMyDBREMOVEFILEMyDB_FG1_Dat1
调整tempdb数据库的文件属性.sql:
SQLcode
--A.将tempdb数据库的主数据文件大小设置为10MB.ALTERDATABASEtempdbMODIFYFILE(name=tempdev,size=100MB)100GO
--B.将tempdb数据库的主数据文件移动到指定的磁盘分区上,并且为其添加一个数据文件.--移动主数据文件ALTERDATABASEtempdbMODIFYFILE(NAME='tempdev',FILENAME='d:\tempdb.mdf')
--添加次要数据文件
ALTERDATABASEtempdbADDFILE(NAME='tempdata_1',FILENAME='d:\tempdb_data_1.ndf')
日期概念理解中的一些测试.sql:
SQLcode
--A.测试datetime精度问题DECLARE@tTABLE(datechar(2121))21INSERT@tSELECT'1900-1-100:00:00.000'INSERT@tSELECT'1900-1-100:00:00.001'INSERT@tSELECT'1900-1-100:00:00.009'INSERT@tSELECT'1900-1-100:00:00.002'INSERT@tSELECT'1900-1-100:00:00.003'INSERT@tSELECT'1900-1-100:00:00.004'INSERT@tSELECT'1900-1-100:00:00.005'INSERT@tSELECT'1900-1-100:00:00.006'INSERT@tSELECT'1900-1-100:00:00.007'INSERT@tSELECT'1900-1-100:00:00.008'SELECTdate,转换后的日期=CAST(dateasdatetime)FROM@t
/*--结果
date
转换后的日期
----------------------------------------------1900-1-100:00:00.0001900-01-0100:00:00.0001900-1-100:00:00.0011900-01-0100:00:00.0001900-1-100:00:00.0091900-01-0100:00:00.0101900-1-100:00:00.0021900-01-0100:00:00.0031900-1-100:00:00.0031900-01-0100:00:00.0031900-1-100:00:00.0041900-01-0100:00:00.003
1900-1-100:00:00.0051900-01-0100:00:00.0071900-1-100:00:00.0061900-01-0100:00:00.0071900-1-100:00:00.0071900-01-0100:00:00.0071900-1-100:00:00.0081900-01-0100:00:00.007
(所影响的行数为10行)--*/GO
--B.对于datetime类型的纯日期和时间的十六进制表示DECLARE@dtdatetime
--单纯的日期SET@dt='1900-1-2'SELECTCAST(@dtasbinary(8))8--结果:0x0000000100000000
--单纯的时间SET@dt='00:00:01'SELECTCAST(@dtasbinary(8))8--结果:0x000000000000012CGO
--C.对于smalldatetime类型的纯日期和时间的十六进制表示DECLARE@dtsmalldatetime
--单纯的日期SET@dt='1900-1-2'SELECTCAST(@dtasbinary(4))4
--结果:0x00010000
--单纯的时间SET@dt='00:10'SELECTCAST(@dtasbinary(4))4--结果:0x0000000A
CONVERT在日期转换中的使用示例.sql:
SQLcode
--字符转换为日期时,Style的使用
--1.Style=101时,表示日期字符串为:mm/dd/yyyy格式SELECTCONVERT(datetime,'11/1/2003',101101)101--结果:2003-11-0100:00:00.000
--2.Style=101时,表示日期字符串为:dd/mm/yyyy格式SELECTCONVERT(datetime,'11/1/2003',103103)103--结果:2003-01-1100:00:00.000
/*==日期转换为字符串==*/DECLARE@dtdatetimeSET@dt='2003-1-11'
--1.Style=101时,表示将日期转换为:mm/dd/yyyy格式SELECTCONVERT(varchar,@dt,101101)101--结果:01/11/2003
--2.Style=103时,表示将日期转换为:dd/mm/yyyy格式SELECTCONVERT(varchar,@dt,103103)103--结果:11/01/2003
/*==这是很多人经常犯的错误,对非日期型转换使用日期的style样式==*/SELECTCONVERT(varchar,'2003-1-11',101101)101--结果:2003-1-11
SETDATEFORMAT对日期处理的影响.sql
SQLcode
--1./*--说明SETDATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响但不影响明确指定了style的CONVERT处理.--*/
--示例,在下面的示例中,第一个CONVERT转换未指定style,转换的结果受SETDATAFORMAT的影响,第二个CONVERT转换指定了style,转换结果受style的影响.--设置输入日期顺序为日/月/年SETDATEFORMATDMY
--不指定Style参数的CONVERT转换将受到SETDATEFORMAT的影响SELECTCONVERT(datetime,'2-1-2005')--结果:2005-01-0200:00:00.000
--指定Style参数的CONVERT转换不受SETDATEFORMAT的影响SELECTCONVERT(datetime,'2-1-2005',101101)101--结果:2005-02-0100:00:00.000GO
--2./*--说明
如果输入的日期包含了世纪部分,则对日期进行解释处理时年份的解释不受SETDATEFORMAT设置的影响.--*/
--示例,在下面的代码中,同样的SETDATEFORMAT设置,输入日期的世纪部分与不输入日期的世纪部分,解释的日期结果不同.DECLARE@dtdatetime
--设置SETDATEFORMAT为:月日年SETDATEFORMATMDY
--输入的日期中指定世纪部分SET@dt='01-2002-03'SELECT@dt--结果:2002-01-0300:00:00.000
--输入的日期中不指定世纪部分SET@dt='01-02-03'SELECT@dt--结果:2003-01-0200:00:00.000GO
--3./*--说明
如果输入的日期不包含日期分隔符,那么SQLServer在对日期进行解释时将忽略SETDATEFORMAT的设置.--*/
--示例,在下面的代码中,不包含日期分隔符的字符日期,在不同的SETDATEFORMAT设置下,其解释的结果是一样的.DECLARE@dtdatetime
--设置SETDATEFORMAT为:月日年SETDATEFORMATMDYSET@dt='010203'SELECT@dt--结果:2001-02-0300:00:00.000
--设置SETDATEFORMAT为:日月年SETDATEFORMATDMYSET@dt='010203'SELECT@dt--结果:2001-02-0300:00:00.000
--输入的日期中包含日期分隔符SET@dt='01-02-03'SELECT@dt--结果:2003-02-0100:00:00.000
SETLANGUAGE对日期处理的影响示例.sql
SQLcode
--以下示例演示了在不同的语言环境(SETLANGUAGE)下,DATENAME与CONVERT函数的不同结果.USEmaster
--设置会话的语言环境为:EnglishSETLANGUAGEN'English'SELECTDATENAME(Month,GETDATE())AS[Month],DATENAME(Weekday,GETDATE())AS[Weekday],CONVERT(varchar,GETDATE(),109AS[CONVERT]109)109/*--结果:MonthWeekdayCONVERT
---------------------------------------------------------MarchTuesdayMar152005--*/8:59PM
--设置会话的语言环境为:简体中文SETLANGUAGEN'简体中文'SELECTDATENAME(Month,GETDATE())AS[Month],DATENAME(Weekday,GETDATE())AS[Weekday],CONVERT(varchar,GETDATE(),109AS[CONVERT]109)109/*--结果MonthWeekdayCONVERT
-------------------------------------------------------------------05星期四051920052:49:20:607PM
--*/
日期格式化处理.sql
SQLcode
DECLARE@dtdatetimeSET@dt=GETDATE()
--1.短日期格式:yyyy-m-dSELECTREPLACE(CONVERT(varchar(1010),@dt,120120),N'-0','-')10120
--2.长日期格式:yyyy年mm月dd日--A.方法1SELECTSTUFF(STUFF(CONVERT(char(8),@dt,1125,0,N'年'),8,0,N'月8112),5011280')+N'日'--B.方法2SELECTDATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'
--3.长日期格式:yyyy年m月d日SELECTDATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt)ASvarchar)+N'月'+DATENAME(Day,@dt)+N'日'
--4.完整日期+时间格式:yyyy-mm-ddhh:mi:ss:mmmSELECTCONVERT(char(1111),@dt,120120)+CONVERT(char(1212),@dt,114114)1112012114
日期推算处理.sql
SQLcode
DECLARE@dtdatetimeSET@dt=GETDATE()
DECLARE@numberintSET@number=33
--1.指定日期该年的第一天或最后一天--A.年的第一天SELECTCONVERT(char(5),@dt,1205120)+'1-1'120
--B.年的最后一天SELECTCONVERT(char(5),@dt,1205120)+'12-31'120
--2.指定日期所在季度的第一天或最后一天--A.季度的第一天SELECTCONVERT(datetime,CONVERT(char(8),8DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt)-2,32@dt),120)+'1')120
--B.季度的最后一天(CASE判断法)SELECTCONVERT(datetime,CONVERT(char(8),8DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt),3@dt),120)120+CASEWHENDATEPART(Quarter,@dt)in(1,4)14THEN'31'ELSE'30'END)
--C.季度的最后一天(直接推算法)SELECTDATEADD(Day,-1,1CONVERT(char(8),8DATEADD(Month,1+DATEPART(Quarter,@dt)*3-Month(@dt),3@dt),120)+'1')120
--3.指定日期所在月份的第一天或最后一天--A.月的第一天SELECTCONVERT(datetime,CONVERT(char(8),@dt,1208120)+'1')120
--B.月的最后一天SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),1201120)81120+'1')
--C.月的最后一天(容易使用的错误方法)SELECTDATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))1
--4.指定日期所在周的任意一天SELECTDATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5.指定日期所在周的任意星期几--A.星期天做为一周的第1天
SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST@@DATEFIRST@@DATEFIRST1)%7,@dt)7
--B.
星期一做为一周的第1天
SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST@@DATEFIRST@@DATEFIRST2)%7-1,@dt)71
特殊日期加减函数.sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_DateADD]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_DateADD]GO
/*--特殊日期加减函数
对于日期指定部分的加减,使用DATEADD函数就可以轻松实现.在实际的处理中,还有一种比较另类的日期加减处理就是在指定的日期中,加上(或者减去)多个日期部分比如将2005年3月11日,加上1年3个月11天2小时.对于这种日期的加减处理,DATEADD函数的力量就显得有点不够.
本函数实现这样格式的日期字符串加减处理:y-m-dh:m:s.m|-y-m-dh:m:s.m说明:要加减的日期字符输入方式与日期字符串相同.日期与时间部分用空格分隔
最前面一个字符如果是减号(-)的话,表示做减法处理,否则做加法处理.如果日期字符只包含数字,则视为日期字符中,仅包含天的信息.--*/
/*--调用示例
SELECTdbo.f_DateADD(GETDATE(),'11:10')--*/
CREATEFUNCTIONdbo.f_DateADD(@Date@DateStrdatetime,varchar(2323)23
)RETURNSdatetimeASBEGINDECLARE@bzint,@svarchar(1212),@iint12
IF@DateStrISNULLOR@DateISNULLOR(CHARINDEX('.',@DateStr)>00AND@DateStrNOTLIKE'%[:]%[:]%.%')RETURN(NULL)IF@DateStr=''RETURN(@Date)
SELECT@bz=CASEWHENLEFT(@DateStr,1)='-'THEN-111ELSE1END,@DateStr=CASEWHENLEFT(@Date,1)='-'1
THENSTUFF(RTRIM(LTRIM(@DateStr)),1,1,'')11ELSERTRIM(LTRIM(@DateStr))END
IFCHARINDEX('',@DateStr)>11ORCHARINDEX('-',@DateStr)>11OR(CHARINDEX('.',@DateStr)=00ANDCHARINDEX(':',@DateStr)=0)0BEGINSELECT@i=CHARINDEX('',@DateStr+''),@s=REVERSE(LEFT(@DateStr,@i-1))+'-'1,@DateStr=STUFF(@DateStr,1,@i,'')1,@i=00WHILE@s>''and@i<33SELECT@Date=CASE@iWHEN0THENDATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)1WHEN1THENDATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)1WHEN2THENDATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)1END,@s=STUFF(@s,1,CHARINDEX('-',@s),''),1@i=@i+11ENDIF@DateStr>''BEGINIFCHARINDEX('.',@DateStr)>00SELECT@Date=DATEADD(Millisecond
,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateSt1r),''),@Date),@DateStr=LEFT(@DateStr,CHARINDEX('.',@DateStr)1)+':',@i=00ELSESELECT@DateStr=@DateStr+':',@i=00WHILE@DateStr>''and@i<33SELECT@Date=CASE@iWHEN0THENDATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)1WHEN1THENDATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)1WHEN2THENDATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)1END,@DateStr=STUFF(@DateStr,1,CHARINDEX(':',@DateSt1r),''),@i=@i+11END
RETURN(@Date)ENDGO
查询指定日期段内过生日的人员.sql
SQLcode
--测试数据DECLARE@tTABLE(IDint,Namevarchar(1010),Birthdaydatetime)10
INSERT@tSELECT1,'aa','1999-01-01'UNIONALLSELECT2,'bb','1996-02-29'UNIONALLSELECT3,'bb','1934-03-01'UNIONALLSELECT4,'bb','1966-04-01'UNIONALLSELECT5,'bb','1997-05-01'UNIONALLSELECT6,'bb','1922-11-21'UNIONALLSELECT7,'bb','1989-12-11'
DECLARE@dt1datetime,@dt2datetime
--查询2003-12-05至2004-02-28生日的记录SELECT@dt1='2003-12-05',@dt2='2004-02-28'SELECT*FROM@tWHEREDATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)BETWEEN@dt1AND@dt2ORDATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)BETWEEN@dt1AND@dt2/*--结果IDNameBirthday
---------------------------------------------------------17aabb1999-01-0100:00:00.0001989-12-1100:00:00.000--*/
--查询2003-12-05至2006-02-28生日的记录SET@dt2='2006-02-28'SELECT*FROM@tWHEREDATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)BETWEEN@dt1AND@dt2
ORDATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)BETWEEN@dt1AND@dt2/*--查询结果IDNameBirthday
----------------------------------------------------------127aabbbb1999-01-0100:00:00.0001996-02-2900:00:00.0001989-12-1100:00:00.000--*/
生成日期列表的函数.sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_getdate]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_getdate]GO
/*--生成日期列表
生成指定年份的工作日/休息日列表
--邹建2003.12(引用请保留此信息)--*/
/*--调用示例
--查询2003年的工作日列表SELECT*FROMdbo.f_getdate(2003,0)
--查询2003年的休息日列表SELECT*FROMdbo.f_getdate(2003,1)
--查询2003年全部日期列表SELECT*FROMdbo.f_getdate(2003,NULL)--*/CREATEFUNCTIONdbo.f_getdate(@yearint,@bzbit--要查询的年份
--@bz=0查询工作日,@bz=1查询休息日,@bzISNULL查询全部日期
)RETURNS@reTABLE(idintidentity(1,1),Datedatetime,Weekdayn11varchar(3))3ASBEGINDECLARE@tbTABLE(IDintIDENTITY(0,1),Datedatetime)01INSERTINTO@tb(Date)SELECTTOP366DATEADD(Year,@YEAR-1901900,'1900-1-1')FROMsysobjectsa,sysobjectsbUPDATE@tbSETDate=DATEADD(DAY,id,Date)DELETEFROM@tbWHEREDate>DATEADD(Year,@YEAR-19001900,'1900-121900-31')
IF@bz=00INSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)FROM@tbWHERE(DATEPART(Weekday,Date)+@@DATEFIRST1)%7BETWEEN@@DATEFIRST-17@@DATEFIRST1AND5ELSEIF@bz=11INSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)
FROM@tbWHERE(DATEPART(Weekday,Date)+@@DATEFIRST1)%7IN(0,6)@@DATEFIRST-1706@@DATEFIRSTELSEINSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)FROM@tb
RETURNENDGO
/*====================================================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_getdate]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_getdate]GO
/*--生成列表
生成指定日期段的日期列表
--邹建2005.03(引用请保留此信息)--*/
/*--调用示例
--查询工作日
SELECT*FROMdbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日SELECT*FROMdbo.f_getdate('2005-1-3','2005-4-5',1)
--查询全部日期SELECT*FROMdbo.f_getdate('2005-1-3','2005-4-5',NULL)--*/
CREATEFUNCTIONdbo.f_getdate(@begin_dateDatetime,@end_dateDatetime,@bzbit--要查询的开始日期--要查询的结束日期
--@bz=0查询工作日,@bz=1查询休息日,@bzISNULL查询全部日期
)RETURNS@reTABLE(idintidentity(1,1),Datedatetime,Weekdayn11varchar(3))3ASBEGINDECLARE@tbTABLE(IDintIDENTITY(0,1),abit)01INSERTINTO@tb(a)SELECTTOP3660FROMsysobjectsa,sysobjectsb
IF@bz=00WHILE@begin_date<=@end_dateBEGININSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)FROM(SELECTDate=DATEADD(Day,ID,@begin_date)
FROM@tb)aWHEREDate<=@end_dateAND(DATEPART(Weekday,Date)+@@DATEFIRST-1)%7BE@@DATEFIRST17@@DATEFIRSTTWEEN1AND5SET@begin_date=DATEADD(Day,366366,@begin_date)366ENDELSEIF@bz=11WHILE@begin_date<=@end_dateBEGININSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)FROM(SELECTDate=DATEADD(Day,ID,@begin_date)FROM@tb)aWHEREDate<=@end_dateAND(DATEPART(Weekday,Date)+@@DATEFIRST1)%7in@@DATEFIRST-17@@DATEFIRST(0,6)06SET@begin_date=DATEADD(Day,366366,@begin_date)366ENDELSEWHILE@begin_date<=@end_dateBEGININSERTINTO@re(Date,Weekday)SELECTDate,DATENAME(Weekday,Date)FROM(SELECTDate=DATEADD(Day,ID,@begin_date)FROM@tb)aWHEREDate<=@end_dateSET@begin_date=DATEADD(Day,366366,@begin_date)366
END
RETURNENDGO
工作日处理函数(标准节假日).sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_WorkDay]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_WorkDay]GO
--计算两个日期相差的工作天数CREATEFUNCTIONf_WorkDay(@dt_begindatetime,@dt_enddatetime--计算的开始日期--计算的结束日期
)RETURNSintASBEGINDECLARE@workdayint,@iint,@bzbit,@dtdatetimeIF@dt_begin>@dt_endSELECT@bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@d1tELSESET@bz=00SELECT@i=DATEDIFF(Day,@dt_begin,@dt_end)+1,1@workday=@i/7*5,75
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)77WHILE@dt_begin<=@dt_endBEGINSELECT@workday=CASEWHEN(@@DATEFIRST@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7@@DATEFIRST17BETWEEN1AND5THEN@workday+1ELSE@workdayEND,1@dt_begin=@dt_begin+11ENDRETURN(CASEWHEN@bz=1THEN-@workdayELSE@workdayEND)1ENDGO
/*=================================================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_WorkDayADD]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_WorkDayADD]GO
--在指定日期上,增加指定工作天数后的日期CREATEFUNCTIONf_WorkDayADD(@date@workdayintdatetime,--基础日期
--要增加的工作日数
)RETURNSdatetimeAS
BEGINDECLARE@bzint--增加整周的天数SELECT@bz=CASEWHEN@workday<0THEN-1ELSE1END01,@date=DATEADD(Week,@workday/5,@date)5,@workday=@workday%55--增加不是整周的工作天数WHILE@workday<>00SELECT@date=DATEADD(Day,@bz,@date),@workday=CASEWHEN(@@DATEFIRST@@DATEFIRST+DATEPART(Weekday,@d@@DATEFIRSTate)-1)%7BETWEEN1AND517THEN@workday-@bzELSE@workdayEND--避免处理后的日期停留在非工作日上WHILE(@@DATEFIRST@@DATEFIRST+DATEPART(Weekday,@date)-1)%7in(0,6)@@DATEFIRST1706SET@date=DATEADD(Day,@bz,@date)RETURN(@date)END
工作日处理函数(自定义节假日).sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[tb_Holiday]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[tb_Holiday]GO
--定义节假日表CREATETABLEtb_Holiday(HDatesmalldatetimeprimarykeyclustered,--节假日期
Namenvarchar(50notnull)50)50GO
--假日名称
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_WorkDay]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_WorkDay]GO
--计算两个日期之间的工作天数CREATEFUNCTIONf_WorkDay(@dt_begindatetime,@dt_enddatetime--计算的开始日期--计算的结束日期
)RETURNSintASBEGINIF@dt_begin>@dt_endRETURN(DATEDIFF(Day,@dt_begin,@dt_end)+1-(1SELECTCOUNT(*)FROMtb_HolidayWHEREHDateBETWEEN@dt_beginAND@dt_end))RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)+1-(1SELECTCOUNT(*)FROMtb_HolidayWHEREHDateBETWEEN@dt_endAND@dt_begin)))ENDGO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_WorkDayADD]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[f_WorkDayADD]GO
--在指定日期上增加工作天数CREATEFUNCTIONf_WorkDayADD(@date@workdayintdatetime,--基础日期
--要增加的工作日数
)RETURNSdatetimeASBEGINIF@workday>00WHILE@workday>00SELECT@date=@date+@workday,@workday=count(*)FROMtb_HolidayWHEREHDateBETWEEN@dateAND@date+@workdayELSEWHILE@workday<00SELECT@date=@date+@workday,@workday=-count(*)FROMtb_HolidayWHEREHDateBETWEEN@dateAND@date+@workdayRETURN(@date)END
计算工作时间的函数.sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[tb_worktime]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[tb_worktime]GO
--定义工作时间表CREATETABLEtb_worktime(IDintidentity(1,1)PRIMARYKEY,11--序号--工作
time_startsmalldatetime,的开始时间time_endsmalldatetime,结束时间worktimeASDATEDIFF(Minute,time_start,time_end)数(分钟))GO
--工作的
--工作时
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_WorkTime]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_WorkTime]GO
--计算两个日期之间的工作时间CREATEFUNCTIONf_WorkTime(@date_begindatetime,@date_enddatetime--计算的开始时间--计算的结束时间
)RETURNSintASBEGINDECLARE@worktimeintIFDATEDIFF(Day,@date_begin,@date_end)=00SELECT@worktime=SUM(DATEDIFF(Minute,CASEWHENCONVERT(VARCHAR,@date_begin,108108)>time_sta108rt
THENCONVERT(VARCHAR,@date_begin,108108)108ELSEtime_startEND,CASEWHENCONVERT(VARCHAR,@date_end,108108)
CONVERT(VARCHAR,@date_begin,108108)108ANDtime_starttime_108startTHENDATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108108),time_end)108ELSEworktimeEND)FROMtb_worktimeWHEREtime_end>CONVERT(VARCHAR,@date_begin,10108))+(SELECTSUM(CASEWHENCONVERT(VARCHAR,@date_end,108108) WHENDATEDIFF(Day,@date_begin,@date_end)>11THEN(DATEDIFF(Day,@date_begin,@date_end)-1)1*(SELECTSUM(worktime)FROMtb_worktime)ELSE0ENDRETURN(@worktime)END
复杂年月处理.sql
SQLcode
--定义基本数字表declare@T1table(代码int,名称varchar(1010),参加时间datetime,10终止时间datetime)insertinto@T1select1212,'单位1','2003/04/01','2004/05/01'unionallselect2222,'单位2','2001/02/01','2003/02/01'unionallselect4242,'单位3','2000/04/01','2003/05/01'unionallselect2525,'单位5','2003/04/01','2003/05/01'
--定义年表declare@NBtable(代码int,名称varchar(1010),年份int)10insertinto@NBselect1212,'单位1',20032003unionallselect1212,'单位1',20042004unionallselect22,'单位2',2001222001unionallselect2222,'单位2',20022002unionallselect2222,'单位2',20032003
--定义月表declare@YBtable(代码int,名称varchar(1010),年份int,月份varch10ar(2))2
insertinto@YBselect12,'单位1',2003122003,'04'2003unionallselect2222,'单位2',20012001,'01'2001unionallselect2222,'单位2',20012001,'12'2001
--为年表+月表数据处理准备临时表selecttop8246y=identity(int,17531)1753,11753into#tbyfrom(selectidfromsyscolumns)a,(selectidfromsyscolumns)b,(selectidfromsyscolumns)c
--为月表数据处理准备临时表selecttop12m=identity(int,1,1)11into#tbmfromsyscolumns
/*--数据处理--*/--年表数据处理selecta.*from(selecta.代码,a.名称,年份=b.yfrom@T1a,#tbybwhereb.ybetweenyear(参加时间)andyear(终止时间))aleftjoin@NBbona.代码=b.代码anda.年份=b.年份whereb.代码isnull
--月表数据处理selecta.*from(
selecta.代码,a.名称,年份=b.y,月份=right('00'+cast(c.masvarchar),2)2from@T1a,#tbyb,#tbmcwhereb.y*100100+c.mbetweenconvert(varchar(6),参加时间,112112)1006112andconvert(varchar(6),终止时间,1126112)112)aleftjoin@YBbona.代码=b.代码anda.年份=b.年份anda.月份=b.月份whereb.代码isnullorderbya.代码,a.名称,a.年份,a.月份
--删除数据处理临时表droptable#tby,#tbm
交叉表.sql
SQLcode
--示例
--示例数据createtabletb(IDint,Timedatetime)inserttbselect1,'2005/01/2416:20'unionallselect2,'2005/01/2322:45'unionallselect3,'2005/01/230:30'unionallselect4,'2005/01/214:28'unionallselect5,'2005/01/2013:22'unionallselect6,'2005/01/1920:30'unionallselect7,'2005/01/1918:23'unionallselect8,'2005/01/189:14'unionallselect9,'2005/01/1818:04'go
--查询处理:selectcasewhengrouping(b.Time)=1then'Total'elseb.Tim1eend,[Mon]=sum(casea.weekwhen1then1else0end),[Tue]=sum(casea.weekwhen2then1else0end),[Wed]=sum(casea.weekwhen3then1else0end),[Thu]=sum(casea.weekwhen4then1else0end),[Fri]=sum(casea.weekwhen5then1else0end),[Sat]=sum(casea.weekwhen6then1else0end),[Sun]=sum(casea.weekwhen0then1else0end),[Total]=count(a.week)from(selectTime=convert(char(5),dateadd(hour,-1,Time),1085108)1108--时间交界点是1am,所以减1小时,避免进行跨天处理,week=(@@datefirst@@datefirst+datepart(weekday,Time)-1)%7@@datefirst17--考虑@@datefirst对datepart的影响fromtb)arightjoin(selectid=1,a='16:00',b='19:59',Time='[5pm-9pm)'unional1lselectid=2,a='20:00',b='23:59',Time='[9pm-1am)'unional2lselectid=3,a='00:00',b='02:59',Time='[1am-4am)'unional3lselectid=4,a='03:00',b='07:29',Time='[4am-8:30am)'union4allselectid=5,a='07:30',b='11:59',Time='[8:30am-1pm)'union5allselectid=6,a='12:00',b='15:59',Time='[1pm-5pm)'6
)bona.Time>=b.aanda.Time --删除测试droptabletb
/*--测试结果
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Total
------------------------------------------------------[5pm-9pm)[9pm-1am)[1am-4am)0001000102200000200000110001001000000002000023201129
[4am-8:30am)0[8:30am-1pm)0[1pm-5pm)Total11
(所影响的行数为7行)--*/
任意两个时间之间的星期几的次数-横.sql
SQLcode
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_weekdaycount]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_weekdaycount]GO
/*--计算任意两个时间之间的星期几的次数(横向显示)
本方法直接判断@@datefirst做对应处理不受sp_language及setdatefirst的影响
--邹建2004.08(引用请保留此信息)--*/
/*--调用示例
select*fromf_weekdaycount('2004-9-01','2004-9-02')--*/createfunctionf_weekdaycount(@dt_begindatetime,@dt_enddatetime)returnstableasreturn(select跨周数,周一=caseawhen-1thencasewhen1betweenbandcthen1els1e0endwhen0thencasewhenb<=1then1else0end1+casewhenc>=1then1else0end1elsea+casewhenb<=1then1else0end1+casewhenc>=1then1else0end1end,周二=casea
when-1thencasewhen2betweenbandcthen1els1e0endwhen0thencasewhenb<=2then1else0end2+casewhenc>=2then1else0end2elsea+casewhenb<=2then1else0end2+casewhenc>=2then1else0end2end,周三=caseawhen-1thencasewhen3betweenbandcthen1els1e0endwhen0thencasewhenb<=3then1else0end3+casewhenc>=3then1else0end3elsea+casewhenb<=3then1else0end3+casewhenc>=3then1else0end3end,周四=caseawhen-1thencasewhen4betweenbandcthen1els1e0endwhen0thencasewhenb<=4then1else0end4+casewhenc>=4then1else0end4elsea+casewhenb<=4then1else0end4+casewhenc>=4then1else0end4end,周五=caseawhen-1thencasewhen5betweenbandcthen1els1e0endwhen0thencasewhenb<=5then1else0end5+casewhenc>=5then1else0end5elsea+casewhenb<=5then1else0end5
+casewhenc>=5then1else0end5end,周六=caseawhen-1thencasewhen6betweenbandcthen1els1e0endwhen0thencasewhenb<=6then1else0end6+casewhenc>=6then1else0end6elsea+casewhenb<=6then1else0end6+casewhenc>=6then1else0end6end,周日=caseawhen-1thencasewhen0betweenbandcthen1els1e0endwhen0thencasewhenb<=0then1else0end0+casewhenc>=0then1else0end0elsea+casewhenb<=0then1else0end0+casewhenc>=0then1else0end0endfrom(select跨周数=casewhen@dt_begin<@dt_endthen(datediff(day,@dt_begin,@dt_end)+7)/777else(datediff(day,@dt_end,@dt_begin)+7)/7end77,a=casewhen@dt_begin<@dt_endthendatediff(week,@dt_begin,@dt_end)-11elsedatediff(week,@dt_end,@dt_begin)-1end1,b=casewhen@dt_begin<@dt_endthen(@@datefirst@@datefirst+datepart(weekday,@dt_begin)@@datefirst1)%77
else(@@datefirst@@datefirst+datepart(weekday,@dt_end)-1)%@@datefirst17end,c=casewhen@dt_begin<@dt_endthen(@@datefirst@@datefirst+datepart(weekday,@dt_end)-1)%@@datefirst17else(@@datefirst@@datefirst+datepart(weekday,@dt_begin)@@datefirst1)%7end)a7)go
统计--交叉表+日期+优先.sql
SQLcode
--交叉表,根据优先级取数据,日期处理
createtabletb(qidint,ridnvarchar(4),tagnamenvarchar(10410),st10arttimesmalldatetime,endtimesmalldatetime,startweekdayint,endweekdayint,startdatesmalldatetime,enddatesmalldatetime,dint)inserttbselect1,'A1','未订','08:00','09:00',11,null,11,55,null,55,null
unionallselect1,'A1','未订','09:00','10:00',11,null,11
unionallselect1,'A1','未订','10:00','11:00',11,null,11
,55
,null
unionallselect1,'A1','装修','08:00','09:00',null,null,'20051-18','2005-1-19',22
--unionallselect1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2unionallselect1,'A1','装修','10:00','11:00',null,null,'20051-18','2005-1-19',22unionallselect1,'A2','未订','08:00','09:00',11,null,11,55,null,55,null
unionallselect1,'A2','未订','09:00','10:00',11,null,11
unionallselect1,'A2','未订','10:00','11:00',11,null,11
,55
,null
--unionallselect1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2unionallselect1,'A2','装修','09:00','10:00',null,null,'20051-18','2005-1-19',22--unionallselect1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2go
/*--楼主这个问题要考虑几个方面
1.取星期时,setdatefirst的影响2.优先级问题3.qid,rid应该是未知的(动态变化的)--*/
--实现的存储过程如下createprocp_qry@datesmalldatetime--要查询的日期as
setnocountondeclare@weekint,@snvarchar(40004000)4000--格式化日期和得到星期select@date=convert(char(1010),@date,120120)10120,@week=(@@datefirst@@datefirst+datepart(weekday,@date)-1)%7@@datefirst17,@s=''selectid=identity(int),*into#tfrom(selecttop100percentqid,rid,tagname,starttime=convert(char(5),starttime,1085108),108endtime=convert(char(5),endtime,1085108)108fromtbwhere(@weekbetweenstartweekdayandendweekday)or(@datebetweenstartdateandenddate)orderbyqid,rid,starttime,ddesc)a
select@s=@s+N',['+rtrim(rid)+N']=max(casewhenqid='+rtrim(qid)+N'andrid=N'''+rtrim(rid)+N'''thentagnameelseN''''end)'from#tgroupbyqid,ridexec('selectstarttime,endtime'+@s+'from#tawherenotexists(select*from#twhereqid=a.qidandrid=a.ridandstarttime=a.starttime
andendtime=a.endtimeandid --调用execp_qry'2005-1-17'execp_qry'2005-1-18'go
--删除测试droptabletbdropprocp_qry
/*--测试结果
starttimeendtimeA1
A2
-----------------------------------08:0009:0010:0009:0010:0011:00未订未订未订未订未订未订
starttimeendtimeA1
A2
-----------------------------------08:0009:0010:0009:0010:0011:00装修未订装修--*/未订装修未订
各种字符串分拆处理函数.sql
SQLcode
--各种字符串分函数
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_splitSTR]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_splitSTR]GO
--3.2.1循环截取法CREATEFUNCTIONf_splitSTR(@svarchar(80008000),8000--待分拆的字符串--数据分隔符
@splitvarchar(1010)10
)RETURNS@reTABLE(colvarchar(100100))100ASBEGINDECLARE@splitlenintSET@splitlen=LEN(@split+'a')-22WHILECHARINDEX(@split,@s)>00BEGININSERT@reVALUES(LEFT(@s,CHARINDEX(@split,@s)-1))1SET@s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')1ENDINSERT@reVALUES(@s)RETURNENDGO
/*==============================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_splitSTR]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_splitSTR]GO
--3.2.3.1使用临时性分拆辅助表法CREATEFUNCTIONf_splitSTR(@svarchar(80008000),--待分拆的字符串8000--数据分隔符
@splitvarchar(1010)10
)RETURNS@reTABLE(colvarchar(100100))100ASBEGIN--创建分拆处理的辅助表(用户定义函数中只能操作表变量)DECLARE@tTABLE(IDintIDENTITY,bbit)INSERT@t(b)SELECTTOP80000FROMsyscolumnsa,syscolumnsb
INSERT@reSELECTSUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)FROM@tWHEREID<=LEN(@s+'a')ANDCHARINDEX(@split,@split+@s,ID)=IDRETURNENDGO
/*==============================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_splitSTR]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_splitSTR]GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[tb_splitSTR]')andobjectproperty(id,N'IsUserTable')=1)1droptable[dbo].[tb_splitSTR]GO
--3.2.3.2使用永久性分拆辅助表法--字符串分拆辅助表SELECTTOP8000ID=IDENTITY(int,1,1)INTOdbo.tb_splitSTR11FROMsyscolumnsa,syscolumnsbGO
--字符串分拆处理函数CREATEFUNCTIONf_splitSTR(@svarchar(80008000),8000varchar(1010)10--待分拆的字符串--数据分隔符
@split
)RETURNSTABLEASRETURN(SELECTcol=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)asvarchar(100100))100FROMtb_splitSTRWHEREID<=LEN(@s+'a')ANDCHARINDEX(@split,@split+@s,ID)=ID)GO
/*==============================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_splitSTR]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_splitSTR]GO
--3.2.5将数据项按数字与非数字再次拆份CREATEFUNCTIONf_splitSTR(@svarchar(80008000),8000@splitvarchar(1010)10--待分拆的字符串--数据分隔符
)RETURNS@reTABLE(Novarchar(100100),Valuevarchar(2020))10020ASBEGIN--创建分拆处理的辅助表(用户定义函数中只能操作表变量)DECLARE@tTABLE(IDintIDENTITY,bbit)INSERT@t(b)SELECTTOP80000FROMsyscolumnsa,syscolumnsb
INSERT@reSELECTNo=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',co1l+'a')-1,'')),1Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))1FROM(SELECTcol=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
FROM@tWHEREID<=LEN(@s+'a')ANDCHARINDEX(@split,@split+@s,ID)=ID)aRETURNENDGO
/*==============================================*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_splitSTR]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_splitSTR]GO
--3.2.6分拆短信数据CREATEFUNCTIONf_splitSTR(@svarchar(80008000))8000RETURNS@reTABLE(splitvarchar(1010),valuevarchar(100100))10100ASBEGINDECLARE@splitsTABLE(splitvarchar(1010),splitlenasLEN(spl10it))INSERT@splits(split)SELECT'AC'UNIONALLSELECT'BC'UNIONALLSELECT'CC'UNIONALLSELECT'DC'DECLARE@pos1int,@pos2int,@splitvarchar(1010),@splitlenin10t
SELECTTOP1@pos1=1,@split=split,@splitlen=splitlen1FROM@splitsWHERE@sLIKEsplit+'%'WHILE@pos1>00BEGINSELECTTOP1@pos2=CHARINDEX(split,@s,@splitlen+1)1FROM@splitsWHERECHARINDEX(split,@s,@splitlen+1)>010ORDERBYCHARINDEX(split,@s,@splitlen+1)1IF@@ROWCOUNT0@@ROWCOUNT=0BEGININSERT@reVALUES(@split,STUFF(@s,1,@splitlen,''))1RETURNENDELSEBEGININSERT@reVALUES(@split,SUBSTRING(@s,@splitlen+1,@1pos2-@splitlen-1))1SELECTTOP1@pos1=1,@split=split,@splitlen=splitlen,@s=STUF1F(@s,1,@pos2-1,'')11FROM@splitsWHERESTUFF(@s,1,@pos2-1,'')LIKEsplit+'%'11ENDENDRETURNEND
GO
各种字符串合并处理示例.sql
SQLcode
--各种字符串分函数
--3.3.1使用游标法进行字符串合并处理的示例.--处理的数据CREATETABLEtb(col1varchar(1010),col2int)10INSERTtbSELECT'a',11UNIONALLSELECT'a',22UNIONALLSELECT'b',11UNIONALLSELECT'b',22UNIONALLSELECT'b',33
--合并处理--定义结果集表变量DECLARE@tTABLE(col1varchar(1010),col2varchar(100100))10100
--定义游标并进行合并处理DECLAREtbCURSORLOCALFORSELECTcol1,col2FROMtbORDERBYcol1,col2
DECLARE@col1_oldvarchar(1010),@col1varchar(1010),@col2int,@sva1010rchar(100100)100OPENtbFETCHtbINTO@col1,@col2SELECT@col1_old=@col1,@s=''
WHILE@@FETCH_STATUS0@@FETCH_STATUS=0BEGINIF@col1=@col1_oldSELECT@s=@s+','+CAST(@col2asvarchar)ELSEBEGININSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))11SELECT@s=','+CAST(@col2asvarchar),@col1_old=@col1ENDFETCHtbINTO@col1,@col2ENDINSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))11CLOSEtbDEALLOCATEtb--显示结果并删除测试数据SELECT*FROM@tDROPTABLEtb/*--结果col1col2
--------------------ab--*/GO1,21,2,3
/*==============================================*/
--3.3.2使用用户定义函数,配合SELECT处理完成字符串合并处理的示例--处理的数据CREATETABLEtb(col1varchar(1010),col2int)10INSERTtbSELECT'a',11UNIONALLSELECT'a',22UNIONALLSELECT'b',11UNIONALLSELECT'b',22UNIONALLSELECT'b',33GO
--合并处理函数CREATEFUNCTIONdbo.f_str(@col1varchar(1010))10RETURNSvarchar(100100)100ASBEGINDECLARE@revarchar(100100)100SET@re=''SELECT@re=@re+','+CAST(col2asvarchar)FROMtbWHEREcol1=@col1RETURN(STUFF(@re,1,1,''))11ENDGO
--调用函数SELECTcol1,col2=dbo.f_str(col1)FROMtbGROUPBYcol1--删除测试DROPTABLEtb
DROPFUNCTIONf_str/*--结果col1col2
--------------------ab--*/GO1,21,2,3
/*==============================================*/
--3.3.3使用临时表实现字符串合并处理的示例--处理的数据CREATETABLEtb(col1varchar(1010),col2int)10INSERTtbSELECT'a',11UNIONALLSELECT'a',22UNIONALLSELECT'b',11UNIONALLSELECT'b',22UNIONALLSELECT'b',33
--合并处理SELECTcol1,col2=CAST(col2asvarchar(100100))100INTO#tFROMtbORDERBYcol1,col2DECLARE@col1varchar(1010),@col2varchar(100100)10100UPDATE#tSET@col2=CASEWHEN@col1=col1THEN@col2+','+col2ELSEcol2END,
@col1=col1,col2=@col2SELECT*FROM#t/*--更新处理后的临时表col1col2
----------------------aabbb--*/--得到最终结果SELECTcol1,col2=MAX(col2)FROM#tGROUPBYcol1/*--结果col1col211,211,21,2,3
--------------------ab--*/--删除测试DROPTABLEtb,#tGO1,21,2,3
/*==============================================*/
--3.3.4.1每组<=2条记录的合并--处理的数据
CREATETABLEtb(col1varchar(1010),col2int)10INSERTtbSELECT'a',11UNIONALLSELECT'a',22UNIONALLSELECT'b',11UNIONALLSELECT'b',22UNIONALLSELECT'c',33
--合并处理SELECTcol1,col2=CAST(MIN(col2)asvarchar)+CASEWHENCOUNT(*)=1THEN''1ELSE','+CAST(MAX(col2)asvarchar)ENDFROMtbGROUPBYcol1DROPTABLEtb/*--结果col1col2
-------------------abc--*/1,21,23
--3.3.4.2每组<=3条记录的合并--处理的数据CREATETABLEtb(col1varchar(1010),col2int)10INSERTtbSELECT'a',11
UNIONALLSELECT'a',22UNIONALLSELECT'b',11UNIONALLSELECT'b',22UNIONALLSELECT'b',33UNIONALLSELECT'c',33
--合并处理SELECTcol1,col2=CAST(MIN(col2)asvarchar)+CASEWHENCOUNT(*)=3THEN','3+CAST((SELECTcol2FROMtbWHEREcol1=a.col1ANDcol2NOTIN(MAX(a.col2),MIN(a.col2)))asvarchar)ELSE''END+CASEWHENCOUNT(*)>=2THEN','+CAST(MAX(col2)asvarcha2r)ELSE''ENDFROMtbaGROUPBYcol1DROPTABLEtb/*--结果col1col2
---------------------abc1,21,2,33
--*/GO
