SQL应用大全(来自CSDN)
设置内存选项:
使用文件及文件组.sql:
调整tempdb数据库的文件属性.sql:
日期概念理解中的一些测试.sql:
SET DATEFORMAT对日期处理的影响.sql
SET LANGUAGE对日期处理的影响示例.sql
日期格式化处理.sql
日期推算处理.sql
- SQL code
-
--设置 min server memory 配置项 EXEC sp_configure N'min server memory (MB)', 0 --设置 max server memory 配置项 EXEC sp_configure N'max server memory (MB)', 256 --使更新生效 RECONFIGURE WITH OVERRIDE
使用文件及文件组.sql:
- SQL code
-
/*--功能说明 下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库 该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件 ALTER DATABASE语句将用户定义文件组指定为默认文件组。 之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。 最后，将文件组中的指定数据文件删除 --*/ --切换到 master 数据库 USE master GO --A. 创建数据库 MyDB CREATE DATABASE MyDB ON PRIMARY --主文件组和主要数据文件 ( NAME='MyDB_Primary', FILENAME= 'c:\MyDB_Prm.mdf'), FILEGROUP MyDB_FG1 --用户定义文件组1 ( NAME = 'MyDB_FG1_Dat1', FILENAME = 'c:\MyDB_FG1_1.ndf'), --次要数据文件1 ( NAME = 'MyDB_FG1_Dat2', FILENAME = 'd:\MyDB_FG1_2.ndf'), --次要数据文件2 FILEGROUP MyDB_FG2 --用户定义文件组2 ( NAME = 'MyDB_FG1_Dat', FILENAME = 'e:\MyDB_FG2.ndf') --次要数据文件 LOG ON --日志文件 ( NAME='MyDB_log', FILENAME ='d:\MyDB.ldf') GO --B. 修改默认数据文件组 ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT GO --切换到新建的数据库 MyDB USE MyDB --C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2 CREATE TABLE MyTable ( cola int PRIMARY KEY , colb char(8) , colc image ) TEXTIMAGE_ON MyDB_FG2 --在用户定义文件组MyDB_FG2上创建索引 CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2 GO --D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1 DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE) --删除数据文件MyDB_FG1_Dat1 ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1
调整tempdb数据库的文件属性.sql:
- SQL code
-
--A. 将tempdb数据库的主数据文件大小设置为10MB。 ALTER DATABASE tempdb MODIFY FILE( name=tempdev, size=100 MB) GO --B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。 --移动主数据文件 ALTER DATABASE tempdb MODIFY FILE ( NAME='tempdev', FILENAME ='d:\tempdb.mdf') --添加次要数据文件 ALTER DATABASE tempdb ADD FILE ( NAME='tempdata_1', FILENAME ='d:\tempdb_data_1.ndf')
日期概念理解中的一些测试.sql:
- SQL code
-
--A. 测试 datetime 精度问题 DECLARE @t TABLE(date char(21)) INSERT @t SELECT '1900-1-1 00:00:00.000' INSERT @t SELECT '1900-1-1 00:00:00.001' INSERT @t SELECT '1900-1-1 00:00:00.009' INSERT @t SELECT '1900-1-1 00:00:00.002' INSERT @t SELECT '1900-1-1 00:00:00.003' INSERT @t SELECT '1900-1-1 00:00:00.004' INSERT @t SELECT '1900-1-1 00:00:00.005' INSERT @t SELECT '1900-1-1 00:00:00.006' INSERT @t SELECT '1900-1-1 00:00:00.007' INSERT @t SELECT '1900-1-1 00:00:00.008' SELECT date,转换后的日期=CAST(date as datetime) FROM @t /*--结果 date 转换后的日期 --------------------- -------------------------- 1900-1-1 00:00:00.000 1900-01-01 00:00:00.000 1900-1-1 00:00:00.001 1900-01-01 00:00:00.000 1900-1-1 00:00:00.009 1900-01-01 00:00:00.010 1900-1-1 00:00:00.002 1900-01-01 00:00:00.003 1900-1-1 00:00:00.003 1900-01-01 00:00:00.003 1900-1-1 00:00:00.004 1900-01-01 00:00:00.003 1900-1-1 00:00:00.005 1900-01-01 00:00:00.007 1900-1-1 00:00:00.006 1900-01-01 00:00:00.007 1900-1-1 00:00:00.007 1900-01-01 00:00:00.007 1900-1-1 00:00:00.008 1900-01-01 00:00:00.007 （所影响的行数为 10 行） --*/ GO --B. 对于 datetime 类型的纯日期和时间的十六进制表示 DECLARE @dt datetime --单纯的日期 SET @dt='1900-1-2' SELECT CAST(@dt as binary(8)) --结果: 0x0000000100000000 --单纯的时间 SET @dt='00:00:01' SELECT CAST(@dt as binary(8)) --结果: 0x000000000000012C GO --C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示 DECLARE @dt smalldatetime --单纯的日期 SET @dt='1900-1-2' SELECT CAST(@dt as binary(4)) --结果: 0x00010000 --单纯的时间 SET @dt='00:10' SELECT CAST(@dt as binary(4)) --结果: 0x0000000A
- SQL code
-
--字符转换为日期时,Style的使用 --1. Style=101时,表示日期字符串为:mm/dd/yyyy格式 SELECT CONVERT(datetime,'11/1/2003',101) --结果:2003-11-01 00:00:00.000 --2. Style=101时,表示日期字符串为:dd/mm/yyyy格式 SELECT CONVERT(datetime,'11/1/2003',103) --结果:2003-01-11 00:00:00.000 /*== 日期转换为字符串 ==*/ DECLARE @dt datetime SET @dt='2003-1-11' --1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式 SELECT CONVERT(varchar,@dt,101) --结果:01/11/2003 --2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式 SELECT CONVERT(varchar,@dt,103) --结果:11/01/2003 /*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/ SELECT CONVERT(varchar,'2003-1-11',101) --结果:2003-1-11
SET DATEFORMAT对日期处理的影响.sql
- SQL code
-
--1. /*--说明 SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响 但不影响明确指定了style的CONVERT处理。 --*/ --示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。 --设置输入日期顺序为 日/月/年 SET DATEFORMAT DMY --不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响 SELECT CONVERT(datetime,'2-1-2005') --结果: 2005-01-02 00:00:00.000 --指定Style参数的CONVERT转换不受SET DATEFORMAT的影响 SELECT CONVERT(datetime,'2-1-2005',101) --结果: 2005-02-01 00:00:00.000 GO --2. /*--说明 如果输入的日期包含了世纪部分，则对日期进行解释处理时 年份的解释不受SET DATEFORMAT设置的影响。 --*/ --示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。 DECLARE @dt datetime --设置SET DATEFORMAT为:月日年 SET DATEFORMAT MDY --输入的日期中指定世纪部分 SET @dt='01-2002-03' SELECT @dt --结果: 2002-01-03 00:00:00.000 --输入的日期中不指定世纪部分 SET @dt='01-02-03' SELECT @dt --结果: 2003-01-02 00:00:00.000 GO --3. /*--说明 如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时 将忽略SET DATEFORMAT的设置。 --*/ --示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。 DECLARE @dt datetime --设置SET DATEFORMAT为:月日年 SET DATEFORMAT MDY SET @dt='010203' SELECT @dt --结果: 2001-02-03 00:00:00.000 --设置SET DATEFORMAT为:日月年 SET DATEFORMAT DMY SET @dt='010203' SELECT @dt --结果: 2001-02-03 00:00:00.000 --输入的日期中包含日期分隔符 SET @dt='01-02-03' SELECT @dt --结果: 2003-02-01 00:00:00.000
SET LANGUAGE对日期处理的影响示例.sql
- SQL code
-
--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。 USE master --设置会话的语言环境为: English SET LANGUAGE N'English' SELECT DATENAME(Month,GETDATE()) AS [Month], DATENAME(Weekday,GETDATE()) AS [Weekday], CONVERT(varchar,GETDATE(),109) AS [CONVERT] /*--结果: Month Weekday CONVERT ------------- -------------- ------------------------------- March Tuesday Mar 15 2005 8:59PM --*/ --设置会话的语言环境为: 简体中文 SET LANGUAGE N'简体中文' SELECT DATENAME(Month,GETDATE()) AS [Month], DATENAME(Weekday,GETDATE()) AS [Weekday], CONVERT(varchar,GETDATE(),109) AS [CONVERT] /*--结果 Month Weekday CONVERT ------------- --------------- ----------------------------------------- 05 星期四 05 19 2005 2:49:20:607PM --*/
日期格式化处理.sql
- SQL code
-
DECLARE @dt datetime SET @dt=GETDATE() --1．短日期格式：yyyy-m-d SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-') --2．长日期格式：yyyy年mm月dd日 --A. 方法1 SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日' --B. 方法2 SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日' --3．长日期格式：yyyy年m月d日 SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日' --4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
日期推算处理.sql
- SQL code
-
DECLARE @dt datetime SET @dt=GETDATE() DECLARE @number int SET @number=3 --1．指定日期该年的第一天或最后一天 --A. 年的第一天 SELECT CONVERT(char(5),@dt,120)+'1-1' --B. 年的最后一天 SELECT CONVERT(char(5),@dt,120)+'12-31' --2．指定日期所在季度的第一天或最后一天 --A. 季度的第一天 SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,@dt)*3-Month(@dt)-2, @dt), 120)+'1') --B. 季度的最后一天（CASE判断法） SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120) +CASE WHEN DATEPART(Quarter,@dt) in(1,4) THEN '31'ELSE '30' END) --C. 季度的最后一天（直接推算法） SELECT DATEADD(Day,-1, CONVERT(char(8), DATEADD(Month, 1+DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120)+'1') --3．指定日期所在月份的第一天或最后一天 --A. 月的第一天 SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1') --B. 月的最后一天 SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1') --C. 月的最后一天（容易使用的错误方法） SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt)) --4．指定日期所在周的任意一天 SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt) --5．指定日期所在周的任意星期几 --A. 星期天做为一周的第1天 SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt) --B. 星期一做为一周的第1天 SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
-
特殊日期加减函数.sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_DateADD]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_DateADD] GO /*--特殊日期加减函数 对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。 在实际的处理中，还有一种比较另类的日期加减处理 就是在指定的日期中，加上（或者减去）多个日期部分 比如将2005年3月11日，加上1年3个月11天2小时。 对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。 本函数实现这样格式的日期字符串加减处理： y-m-d h:m:s.m | -y-m-d h:m:s.m 说明： 要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔 最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。 如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。 --*/ /*--调用示例 SELECT dbo.f_DateADD(GETDATE(),'11:10') --*/ CREATE FUNCTION dbo.f_DateADD( @Date datetime, @DateStr varchar(23) )RETURNS datetime AS BEGIN DECLARE @bz int,@s varchar(12),@i int IF @DateStr IS NULL OR @Date IS NULL OR(CHARINDEX('.',@DateStr)>0 AND @DateStr NOT LIKE '%[:]%[:]%.%') RETURN(NULL) IF @DateStr='' RETURN(@Date) SELECT @bz=CASE WHEN LEFT(@DateStr,1)='-' THEN -1 ELSE 1 END, @DateStr=CASE WHEN LEFT(@Date,1)='-' THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,'') ELSE RTRIM(LTRIM(@DateStr)) END IF CHARINDEX(' ',@DateStr)>1 OR CHARINDEX('-',@DateStr)>1 OR(CHARINDEX('.',@DateStr)=0 AND CHARINDEX(':',@DateStr)=0) BEGIN SELECT @i=CHARINDEX(' ',@DateStr+' ') ,@s=REVERSE(LEFT(@DateStr,@i-1))+'-' ,@DateStr=STUFF(@DateStr,1,@i,'') ,@i=0 WHILE @s>'' and @i<3 SELECT @Date=CASE @i WHEN 0 THEN DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date) WHEN 1 THEN DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date) WHEN 2 THEN DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date) END, @s=STUFF(@s,1,CHARINDEX('-',@s),''), @i=@i+1 END IF @DateStr>'' BEGIN IF CHARINDEX('.',@DateStr)>0 SELECT @Date=DATEADD(Millisecond ,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateStr),''), @Date), @DateStr=LEFT(@DateStr,CHARINDEX('.',@DateStr)-1)+':', @i=0 ELSE SELECT @DateStr=@DateStr+':',@i=0 WHILE @DateStr>'' and @i<3 SELECT @Date=CASE @i WHEN 0 THEN DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date) WHEN 1 THEN DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date) WHEN 2 THEN DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date) END, @DateStr=STUFF(@DateStr,1,CHARINDEX(':',@DateStr),''), @i=@i+1 END RETURN(@Date) END GO
查询指定日期段内过生日的人员.sql
- SQL code
-
--测试数据 DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime) INSERT @t SELECT 1,'aa','1999-01-01' UNION ALL SELECT 2,'bb','1996-02-29' UNION ALL SELECT 3,'bb','1934-03-01' UNION ALL SELECT 4,'bb','1966-04-01' UNION ALL SELECT 5,'bb','1997-05-01' UNION ALL SELECT 6,'bb','1922-11-21' UNION ALL SELECT 7,'bb','1989-12-11' DECLARE @dt1 datetime,@dt2 datetime --查询 2003-12-05 至 2004-02-28 生日的记录 SELECT @dt1='2003-12-05',@dt2='2004-02-28' SELECT * FROM @t WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday) BETWEEN @dt1 AND @dt2 OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday) BETWEEN @dt1 AND @dt2 /*--结果 ID Name Birthday ---------------- ---------------- -------------------------- 1 aa 1999-01-01 00:00:00.000 7 bb 1989-12-11 00:00:00.000 --*/ --查询 2003-12-05 至 2006-02-28 生日的记录 SET @dt2='2006-02-28' SELECT * FROM @t WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday) BETWEEN @dt1 AND @dt2 OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday) BETWEEN @dt1 AND @dt2 /*--查询结果 ID Name Birthday ---------------- ----------------- -------------------------- 1 aa 1999-01-01 00:00:00.000 2 bb 1996-02-29 00:00:00.000 7 bb 1989-12-11 00:00:00.000 --*/
生成日期列表的函数.sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_getdate] GO /*--生成日期列表 生成指定年份的工作日/休息日列表 --邹建 2003.12(引用请保留此信息)--*/ /*--调用示例 --查询 2003 年的工作日列表 SELECT * FROM dbo.f_getdate(2003,0) --查询 2003 年的休息日列表 SELECT * FROM dbo.f_getdate(2003,1) --查询 2003 年全部日期列表 SELECT * FROM dbo.f_getdate(2003,NULL) --*/ CREATE FUNCTION dbo.f_getdate( @year int, --要查询的年份 @bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期 )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3)) AS BEGIN DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime) INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,'1900-1-1') FROM sysobjects a ,sysobjects b UPDATE @tb SET Date=DATEADD(DAY,id,Date) DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,'1900-12-31') IF @bz=0 INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM @tb WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5 ELSE IF @bz=1 INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM @tb WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 IN (0,6) ELSE INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM @tb RETURN END GO /*====================================================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_getdate] GO /*--生成列表 生成指定日期段的日期列表 --邹建 2005.03(引用请保留此信息)--*/ /*--调用示例 --查询工作日 SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0) --查询休息日 SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1) --查询全部日期 SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL) --*/ CREATE FUNCTION dbo.f_getdate( @begin_date Datetime, --要查询的开始日期 @end_date Datetime, --要查询的结束日期 @bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期 )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3)) AS BEGIN DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit) INSERT INTO @tb(a) SELECT TOP 366 0 FROM sysobjects a ,sysobjects b IF @bz=0 WHILE @begin_date<=@end_date BEGIN INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,@begin_date) FROM @tb )a WHERE Date<=@end_date AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5 SET @begin_date=DATEADD(Day,366,@begin_date) END ELSE IF @bz=1 WHILE @begin_date<=@end_date BEGIN INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,@begin_date) FROM @tb )a WHERE Date<=@end_date AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6) SET @begin_date=DATEADD(Day,366,@begin_date) END ELSE WHILE @begin_date<=@end_date BEGIN INSERT INTO @re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,@begin_date) FROM @tb )a WHERE Date<=@end_date SET @begin_date=DATEADD(Day,366,@begin_date) END RETURN END GO
-
工作日处理函数(标准节假日).sql统计--交叉表+日期+优先.sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDay] GO --计算两个日期相差的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime, --计算的开始日期 @dt_end datetime --计算的结束日期 )RETURNS int AS BEGIN DECLARE @workday int,@i int,@bz bit,@dt datetime IF @dt_begin>@dt_end SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt ELSE SET @bz=0 SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1, @workday=@i/7*5, @dt_begin=DATEADD(Day,@i/7*7,@dt_begin) WHILE @dt_begin<=@dt_end BEGIN SELECT @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 THEN @workday+1 ELSE @workday END, @dt_begin=@dt_begin+1 END RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END) END GO /*=================================================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDayADD] GO --在指定日期上,增加指定工作天数后的日期 CREATE FUNCTION f_WorkDayADD( @date datetime, --基础日期 @workday int --要增加的工作日数 )RETURNS datetime AS BEGIN DECLARE @bz int --增加整周的天数 SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END ,@date=DATEADD(Week,@workday/5,@date) ,@workday=@workday%5 --增加不是整周的工作天数 WHILE @workday<>0 SELECT @date=DATEADD(Day,@bz,@date), @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5 THEN @workday-@bz ELSE @workday END --避免处理后的日期停留在非工作日上 WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6) SET @date=DATEADD(Day,@bz,@date) RETURN(@date) END
工作日处理函数(自定义节假日).sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tb_Holiday] GO --定义节假日表 CREATE TABLE tb_Holiday( HDate smalldatetime primary key clustered, --节假日期 Name nvarchar(50) not null) --假日名称 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDay] GO --计算两个日期之间的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime, --计算的开始日期 @dt_end datetime --计算的结束日期 )RETURNS int AS BEGIN IF @dt_begin>@dt_end RETURN(DATEDIFF(Day,@dt_begin,@dt_end) +1-( SELECT COUNT(*) FROM tb_Holiday WHERE HDate BETWEEN @dt_begin AND @dt_end)) RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin) +1-( SELECT COUNT(*) FROM tb_Holiday WHERE HDate BETWEEN @dt_end AND @dt_begin))) END GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDayADD] GO --在指定日期上增加工作天数 CREATE FUNCTION f_WorkDayADD( @date datetime, --基础日期 @workday int --要增加的工作日数 )RETURNS datetime AS BEGIN IF @workday>0 WHILE @workday>0 SELECT @date=@date+@workday,@workday=count(*) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date+@workday ELSE WHILE @workday<0 SELECT @date=@date+@workday,@workday=-count(*) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date+@workday RETURN(@date) END
计算工作时间的函数.sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_worktime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tb_worktime] GO --定义工作时间表 CREATE TABLE tb_worktime( ID int identity(1,1) PRIMARY KEY, --序号 time_start smalldatetime, --工作的开始时间 time_end smalldatetime, --工作的结束时间 worktime AS DATEDIFF(Minute,time_start,time_end) --工作时数(分钟) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkTime]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkTime] GO --计算两个日期之间的工作时间 CREATE FUNCTION f_WorkTime( @date_begin datetime, --计算的开始时间 @date_end datetime --计算的结束时间 )RETURNS int AS BEGIN DECLARE @worktime int IF DATEDIFF(Day,@date_begin,@date_end)=0 SELECT @worktime=SUM(DATEDIFF(Minute, CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start THEN CONVERT(VARCHAR,@date_begin,108) ELSE time_start END, CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end THEN CONVERT(VARCHAR,@date_end,108) ELSE time_end END)) FROM tb_worktime WHERE time_end>CONVERT(VARCHAR,@date_begin,108) AND time_start<CONVERT(VARCHAR,@date_end,108) ELSE SET @worktime =(SELECT SUM(CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end) ELSE worktime END) FROM tb_worktime WHERE time_end>CONVERT(VARCHAR,@date_begin,108)) +(SELECT SUM(CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108)) ELSE worktime END) FROM tb_worktime WHERE time_start<CONVERT(VARCHAR,@date_end,108)) +CASE WHEN DATEDIFF(Day,@date_begin,@date_end)>1 THEN (DATEDIFF(Day,@date_begin,@date_end)-1) *(SELECT SUM(worktime) FROM tb_worktime) ELSE 0 END RETURN(@worktime) END
- SQL code
-
--定义基本数字表 declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime) insert into @T1 select 12,'单位1','2003/04/01','2004/05/01' union all select 22,'单位2','2001/02/01','2003/02/01' union all select 42,'单位3','2000/04/01','2003/05/01' union all select 25,'单位5','2003/04/01','2003/05/01' --定义年表 declare @NB table(代码 int,名称 varchar(10),年份 int) insert into @NB select 12,'单位1',2003 union all select 12,'单位1',2004 union all select 22,'单位2',2001 union all select 22,'单位2',2002 union all select 22,'单位2',2003 --定义月表 declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2)) insert into @YB select 12,'单位1',2003,'04' union all select 22,'单位2',2001,'01' union all select 22,'单位2',2001,'12' --为年表+月表数据处理准备临时表 select top 8246 y=identity(int,1753,1) into #tby from (select id from syscolumns) a, (select id from syscolumns) b, (select id from syscolumns) c --为月表数据处理准备临时表 select top 12 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 between year(参加时间) and year(终止时间) ) a left join @NB b on a.代码=b.代码 and a.年份=b.年份 where b.代码 is null --月表数据处理 select a.* from( select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2) from @T1 a,#tby b,#tbm c where b.y*100+c.m between convert(varchar(6),参加时间,112) and convert(varchar(6),终止时间,112) ) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份 where b.代码 is null order by a.代码,a.名称,a.年份,a.月份 --删除数据处理临时表 drop table #tby,#tbm
交叉表.sql
- SQL code
-
--示例 --示例数据 create table tb(ID int,Time datetime) insert tb select 1,'2005/01/24 16:20' union all select 2,'2005/01/23 22:45' union all select 3,'2005/01/23 0:30' union all select 4,'2005/01/21 4:28' union all select 5,'2005/01/20 13:22' union all select 6,'2005/01/19 20:30' union all select 7,'2005/01/19 18:23' union all select 8,'2005/01/18 9:14' union all select 9,'2005/01/18 18:04' go --查询处理： select case when grouping(b.Time)=1 then 'Total' else b.Time end, [Mon]=sum(case a.week when 1 then 1 else 0 end), [Tue]=sum(case a.week when 2 then 1 else 0 end), [Wed]=sum(case a.week when 3 then 1 else 0 end), [Thu]=sum(case a.week when 4 then 1 else 0 end), [Fri]=sum(case a.week when 5 then 1 else 0 end), [Sat]=sum(case a.week when 6 then 1 else 0 end), [Sun]=sum(case a.week when 0 then 1 else 0 end), [Total]=count(a.week) from( select Time=convert(char(5),dateadd(hour,-1,Time),108) --时间交界点是1am,所以减1小时，避免进行跨天处理 ,week=(@@datefirst+datepart(weekday,Time)-1)%7 --考虑@@datefirst对datepart的影响 from tb )a right join( select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)' )b on a.Time>=b.a and a.Time<b.b group by b.id,b.Time with rollup having grouping(b.Time)=0 or grouping(b.id)=1 go --删除测试 drop table tb /*--测试结果 Mon Tue Wed Thu Fri Sat Sun Total -------------- ----- ----- ----- ----- ----- ------ ---- ------- [5pm - 9pm) 0 1 2 0 0 0 0 3 [9pm - 1am) 0 0 0 0 0 0 2 2 [1am - 4am) 0 0 0 0 0 0 0 0 [4am - 8:30am) 0 0 0 0 1 0 0 1 [8:30am - 1pm) 0 1 0 0 0 0 0 1 [1pm - 5pm) 1 0 0 1 0 0 0 2 Total 1 2 2 1 1 0 2 9 （所影响的行数为 7 行） --*/
任意两个时间之间的星期几的次数-横.sql
- SQL code
-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_weekdaycount] GO /*--计算任意两个时间之间的星期几的次数(横向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 --邹建 2004.08(引用请保留此信息)--*/ /*--调用示例 select * from f_weekdaycount('2004-9-01','2004-9-02') --*/ create function f_weekdaycount( @dt_begin datetime, @dt_end datetime )returns table as return( select 跨周数 ,周一=case a when -1 then case when 1 between b and c then 1 else 0 end when 0 then case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end else a+case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end end ,周二=case a when -1 then case when 2 between b and c then 1 else 0 end when 0 then case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end else a+case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end end ,周三=case a when -1 then case when 3 between b and c then 1 else 0 end when 0 then case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end else a+case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end end ,周四=case a when -1 then case when 4 between b and c then 1 else 0 end when 0 then case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end else a+case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end end ,周五=case a when -1 then case when 5 between b and c then 1 else 0 end when 0 then case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end else a+case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end end ,周六=case a when -1 then case when 6 between b and c then 1 else 0 end when 0 then case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end else a+case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end end ,周日=case a when -1 then case when 0 between b and c then 1 else 0 end when 0 then case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end else a+case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end end from( select 跨周数=case when @dt_begin<@dt_end then (datediff(day,@dt_begin,@dt_end)+7)/7 else (datediff(day,@dt_end,@dt_begin)+7)/7 end ,a=case when @dt_begin<@dt_end then datediff(week,@dt_begin,@dt_end)-1 else datediff(week,@dt_end,@dt_begin)-1 end ,b=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end ,c=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_end)-1)%7 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a ) go
- SQL code
-
--交叉表,根据优先级取数据,日期处理 create table 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 select 1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1 union all select 1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1 union all select 1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1 union all select 1,'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 union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2 union all select 1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1 union all select 1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1 union all select 1,'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 union all select 1,'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 应该是未知的(动态变化的) --*/ --实现的存储过程如下 create proc p_qry @date smalldatetime --要查询的日期 as set nocount on declare @week int,@s nvarchar(4000) --格式化日期和得到星期 select @date=convert(char(10),@date,120) ,@week=(@@datefirst+datepart(weekday,@date)-1)%7 ,@s='' select id=identity(int),* into #t from( select top 100 percent qid,rid,tagname, starttime=convert(char(5),starttime,108), endtime=convert(char(5),endtime,108) from tb where (@week between startweekday and endweekday) or(@date between startdate and enddate) order by 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 group by 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 --删除测试 drop table tb drop proc 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 code
-
--各种字符串分函数 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO --3.2.1 循环截取法 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GO /*==============================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO --3.2.3.1 使用临时性分拆辅助表法 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) FROM @t WHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@split+@s,ID)=ID RETURN END GO /*==============================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1) drop table [dbo].[tb_splitSTR] GO --3.2.3.2 使用永久性分拆辅助表法 --字符串分拆辅助表 SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR FROM syscolumns a,syscolumns b GO --字符串分拆处理函数 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS TABLE AS RETURN( SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100)) FROM tb_splitSTR WHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@split+@s,ID)=ID) GO /*==============================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO --3.2.5 将数据项按数字与非数字再次拆份 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(No varchar(100),Value varchar(20)) AS BEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM 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') AND CHARINDEX(@split,@split+@s,ID)=ID)a RETURN END GO /*==============================================*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO --3.2.6 分拆短信数据 CREATE FUNCTION f_splitSTR(@s varchar(8000)) RETURNS @re TABLE(split varchar(10),value varchar(100)) AS BEGIN DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split)) INSERT @splits(split) SELECT 'AC' UNION ALL SELECT 'BC' UNION ALL SELECT 'CC' UNION ALL SELECT 'DC' DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int SELECT TOP 1 @pos1=1,@split=split,@splitlen=splitlen FROM @splits WHERE @s LIKE split+'%' WHILE @pos1>0 BEGIN SELECT TOP 1 @pos2=CHARINDEX(split,@s,@splitlen+1) FROM @splits WHERE CHARINDEX(split,@s,@splitlen+1)>0 ORDER BY CHARINDEX(split,@s,@splitlen+1) IF @@ROWCOUNT=0 BEGIN INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,'')) RETURN END ELSE BEGIN INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1)) SELECT TOP 1 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'') FROM @splits WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%' END END RETURN END GO
- SQL code
-
--各种字符串分函数 --3.3.1 使用游标法进行字符串合并处理的示例。 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 --合并处理 --定义结果集表变量 DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) --定义游标并进行合并处理 DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(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(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2 END INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT * FROM @t DROP TABLE tb /*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ GO /*==============================================*/ --3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 GO --合并处理函数 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,'')) END GO --调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1 --删除测试 DROP TABLE tb DROP FUNCTION f_str /*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ GO /*==============================================*/ --3.3.3 使用临时表实现字符串合并处理的示例 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 --合并处理 SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tb ORDER BY col1,col2 DECLARE @col1 varchar(10),@col2 varchar(100) UPDATE #t SET @col2=CASE WHEN @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 GROUP BY col1 /*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/ --删除测试 DROP TABLE tb,#t GO /*==============================================*/ --3.3.4.1 每组 <=2 条记录的合并 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'c',3 --合并处理 SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(col2) as varchar) END FROM tb GROUP BY col1 DROP TABLE tb /*--结果 col1 col2 ---------- ---------- a 1,2 b 1,2 c 3 --*/ --3.3.4.2 每组 <=3 条记录的合并 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 UNION ALL SELECT 'c',3 --合并处理 SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=3 THEN ',' +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) ELSE '' END +CASE WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) ELSE '' END FROM tb a GROUP BY col1 DROP TABLE tb /*--结果 col1 col2 ---------- ------------ a 1,2 b 1,2,3 c 3 --*/ GO
