设置内存选项:
-
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
CONVERT在日期转换中的使用示例.sql:
-
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 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
-
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
-
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
-
76
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
