sql大全（三)

工作日处理函数(标准节假日).sql

1. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) 
2. drop function [dbo].[f_WorkDay] 
3. GO 
4.  
5. --计算两个日期相差的工作天数 
6. CREATE FUNCTION f_WorkDay( 
7. @dt_begin datetime,  --计算的开始日期 
8. @dt_end  datetime    --计算的结束日期 
9. )RETURNS int 
10. AS 
11. BEGIN 
12.     DECLARE @workday int,@i int,@bz bit,@dt datetime 
13.     IF @dt_begin>@dt_end 
14.         SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt 
15.     ELSE 
16.         SET @bz=0 
17.     SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1, 
18.         @workday=@i/7*5, 
19.         @dt_begin=DATEADD(Day,@i/7*7,@dt_begin) 
20.     WHILE @dt_begin<=@dt_end 
21.     BEGIN 
22.         SELECT @workday=CASE  
23.             WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 
24.             THEN @workday+1 ELSE @workday END, 
25.             @dt_begin=@dt_begin+1 
26.     END 
27.     RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END) 
28. END 
29. GO 
30.  
31.  
32.  
33. /*=================================================================*/ 
34.  
35. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF')) 
36. drop function [dbo].[f_WorkDayADD] 
37. GO 
38.  
39. --在指定日期上,增加指定工作天数后的日期 
40. CREATE FUNCTION f_WorkDayADD( 
41. @date    datetime,  --基础日期 
42. @workday int       --要增加的工作日数 
43. )RETURNS datetime 
44. AS 
45. BEGIN 
46.     DECLARE @bz int 
47.     --增加整周的天数 
48.     SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END 
49.         ,@date=DATEADD(Week,@workday/5,@date) 
50.         ,@workday=@workday%5 
51.     --增加不是整周的工作天数 
52.     WHILE @workday<>0  
53.         SELECT @date=DATEADD(Day,@bz,@date), 
54.             @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5 
55.                 THEN @workday-@bz ELSE @workday END 
56.     --避免处理后的日期停留在非工作日上 
57.     WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)  
58.         SET @date=DATEADD(Day,@bz,@date) 
59.     RETURN(@date) 
60. END 

工作日处理函数(自定义节假日).sql

1. if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
2. drop table [tb_Holiday] 
3. GO 
4.  
5. --定义节假日表 
6. CREATE TABLE tb_Holiday( 
7. HDate smalldatetime primary key clustered, --节假日期 
8. Name nvarchar(50) not null)             --假日名称 
9. GO 
10.  
11. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) 
12. drop function [dbo].[f_WorkDay] 
13. GO 
14.  
15. --计算两个日期之间的工作天数 
16. CREATE FUNCTION f_WorkDay( 
17. @dt_begin datetime,  --计算的开始日期 
18. @dt_end  datetime   --计算的结束日期 
19. )RETURNS int 
20. AS 
21. BEGIN 
22.     IF @dt_begin>@dt_end 
23.         RETURN(DATEDIFF(Day,@dt_begin,@dt_end) 
24.             +1-( 
25.                 SELECT COUNT(*) FROM tb_Holiday 
26.                 WHERE HDate BETWEEN @dt_begin AND @dt_end)) 
27.     RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin) 
28.         +1-( 
29.             SELECT COUNT(*) FROM tb_Holiday 
30.             WHERE HDate BETWEEN @dt_end AND @dt_begin))) 
31. END 
32. GO 
33.  
34. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF')) 
35. drop function [dbo].[f_WorkDayADD] 
36. GO 
37.  
38. --在指定日期上增加工作天数 
39. CREATE FUNCTION f_WorkDayADD( 
40. @date    datetime,  --基础日期 
41. @workday int       --要增加的工作日数 
42. )RETURNS datetime 
43. AS 
44. BEGIN 
45.     IF @workday>0 
46.         WHILE @workday>0 
47.             SELECT @date=@date+@workday,@workday=count(*) 
48.             FROM tb_Holiday 
49.             WHERE HDate BETWEEN @date AND @date+@workday 
50.     ELSE 
51.         WHILE @workday<0 
52.             SELECT @date=@date+@workday,@workday=-count(*) 
53.             FROM tb_Holiday 
54.             WHERE HDate BETWEEN @date AND @date+@workday 
55.     RETURN(@date) 
56. END 

计算工作时间的函数.sql

1. if exists (select * from dbo.sysobjects where id = object_id(N'[tb_worktime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
2. drop table [tb_worktime] 
3. GO 
4.  
5. --定义工作时间表 
6. CREATE TABLE tb_worktime( 
7.     ID       int identity(1,1) PRIMARY KEY,            --序号 
8.     time_start smalldatetime,                            --工作的开始时间 
9.     time_end  smalldatetime,                           --工作的结束时间 
10.     worktime  AS DATEDIFF(Minute,time_start,time_end)  --工作时数(分钟) 
11. ) 
12. GO 
13.  
14. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkTime]') and xtype in (N'FN', N'IF', N'TF')) 
15. drop function [dbo].[f_WorkTime] 
16. GO 
17.  
18. --计算两个日期之间的工作时间 
19. CREATE FUNCTION f_WorkTime( 
20. @date_begin datetime,  --计算的开始时间 
21. @date_end datetime     --计算的结束时间 
22. )RETURNS int 
23. AS 
24. BEGIN 
25.     DECLARE @worktime int 
26.     IF DATEDIFF(Day,@date_begin,@date_end)=0 
27.         SELECT @worktime=SUM(DATEDIFF(Minute, 
28.             CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start 
29.                 THEN CONVERT(VARCHAR,@date_begin,108) 
30.                 ELSE time_start END, 
31.             CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end 
32.                 THEN CONVERT(VARCHAR,@date_end,108) 
33.                 ELSE time_end END)) 
34.         FROM tb_worktime  
35.         WHERE time_end>CONVERT(VARCHAR,@date_begin,108) 
36.             AND time_start<CONVERT(VARCHAR,@date_end,108) 
37.     ELSE 
38.         SET @worktime 
39.             =(SELECT SUM(CASE 
40.                     WHEN CONVERT(VARCHAR,@date_begin,108)>time_start 
41.                     THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end) 
42.                     ELSE worktime END) 
43.                 FROM tb_worktime  
44.                 WHERE time_end>CONVERT(VARCHAR,@date_begin,108)) 
45.             +(SELECT SUM(CASE  
46.                     WHEN CONVERT(VARCHAR,@date_end,108)<time_end 
47.                     THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108)) 
48.                     ELSE worktime END) 
49.                 FROM tb_worktime  
50.                 WHERE time_start<CONVERT(VARCHAR,@date_end,108)) 
51.             +CASE  
52.                 WHEN DATEDIFF(Day,@date_begin,@date_end)>1  
53.                 THEN (DATEDIFF(Day,@date_begin,@date_end)-1) 
54.                     *(SELECT SUM(worktime) FROM tb_worktime) 
55.                 ELSE 0 END 
56.     RETURN(@worktime) 
57. END 

复杂年月处理.sql

1. --定义基本数字表 
2. declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime) 
3. insert into @T1 
4.     select 12,'单位1','2003/04/01','2004/05/01' 
5.     union all select 22,'单位2','2001/02/01','2003/02/01' 
6.     union all select 42,'单位3','2000/04/01','2003/05/01' 
7.     union all select 25,'单位5','2003/04/01','2003/05/01' 
8.  
9. --定义年表 
10. declare @NB table(代码 int,名称 varchar(10),年份 int) 
11. insert into @NB 
12.     select 12,'单位1',2003 
13.     union all select 12,'单位1',2004 
14.     union all select 22,'单位2',2001 
15.     union all select 22,'单位2',2002 
16.     union all select 22,'单位2',2003 
17.  
18. --定义月表 
19. declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2)) 
20. insert into @YB 
21.     select 12,'单位1',2003,'04' 
22.     union all select 22,'单位2',2001,'01' 
23.     union all select 22,'单位2',2001,'12' 
24.  
25. --为年表+月表数据处理准备临时表 
26. select top 8246 y=identity(int,1753,1) 
27. into #tby from 
28.     (select id from syscolumns) a, 
29.     (select id from syscolumns) b, 
30.     (select id from syscolumns) c 
31.  
32. --为月表数据处理准备临时表 
33. select top 12 m=identity(int,1,1) 
34. into #tbm from syscolumns 
35.  
36. /*--数据处理--*/ 
37. --年表数据处理 
38. select a.* 
39. from( 
40. select a.代码,a.名称,年份=b.y 
41. from @T1 a,#tby b 
42. where b.y between year(参加时间) and year(终止时间) 
43. ) a left join @NB b on a.代码=b.代码 and a.年份=b.年份 
44. where b.代码 is null 
45.  
46. --月表数据处理 
47. select a.* 
48. from( 
49. select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2) 
50. from @T1 a,#tby b,#tbm c 
51. where b.y*100+c.m between convert(varchar(6),参加时间,112)  
52.     and convert(varchar(6),终止时间,112) 
53. ) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份 
54. where b.代码 is null 
55. order by a.代码,a.名称,a.年份,a.月份 
56.  
57. --删除数据处理临时表 
58. drop table #tby,#tbm 

交叉表.sql

1. --示例 
2.  
3. --示例数据 
4. create table tb(ID int,Time datetime) 
5. insert tb select 1,'2005/01/24 16:20' 
6. union all select 2,'2005/01/23 22:45' 
7. union all select 3,'2005/01/23 0:30' 
8. union all select 4,'2005/01/21 4:28' 
9. union all select 5,'2005/01/20 13:22' 
10. union all select 6,'2005/01/19 20:30' 
11. union all select 7,'2005/01/19 18:23' 
12. union all select 8,'2005/01/18 9:14' 
13. union all select 9,'2005/01/18 18:04' 
14. go 
15.  
16. --查询处理： 
17. select     case when grouping(b.Time)=1 then 'Total' else b.Time end, 
18.     [Mon]=sum(case a.week when 1 then 1 else 0 end), 
19.     [Tue]=sum(case a.week when 2 then 1 else 0 end), 
20.     [Wed]=sum(case a.week when 3 then 1 else 0 end), 
21.     [Thu]=sum(case a.week when 4 then 1 else 0 end), 
22.     [Fri]=sum(case a.week when 5 then 1 else 0 end), 
23.     [Sat]=sum(case a.week when 6 then 1 else 0 end), 
24.     [Sun]=sum(case a.week when 0 then 1 else 0 end), 
25.     [Total]=count(a.week) 
26. from( 
27.     select Time=convert(char(5),dateadd(hour,-1,Time),108) 
28.             --时间交界点是1am,所以减1小时，避免进行跨天处理 
29.         ,week=(@@datefirst+datepart(weekday,Time)-1)%7 
30.             --考虑@@datefirst对datepart的影响 
31.     from tb 
32. )a right join( 
33.     select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all 
34.     select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all 
35.     select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all 
36.     select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all 
37.     select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all 
38.     select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)' 
39. )b on a.Time>=b.a and a.Time<b.b 
40. group by b.id,b.Time with rollup 
41. having grouping(b.Time)=0 or grouping(b.id)=1 
42. go 
43.  
44. --删除测试 
45. drop table tb 
46.  
47. /*--测试结果 
48.  
49.                Mon   Tue   Wed   Thu   Fri   Sat   Sun   Total  
50. -------------- ----- ----- ----- ----- ----- ------ ---- ------- 
51. [5pm - 9pm)    0     1     2     0     0     0     0     3 
52. [9pm - 1am)    0     0     0     0     0     0     2     2 
53. [1am - 4am)    0     0     0     0     0     0     0     0 
54. [4am - 8:30am) 0     0     0     0     1     0     0     1 
55. [8:30am - 1pm) 0     1     0     0     0     0     0     1 
56. [1pm - 5pm)    1     0     0     1     0     0     0     2 
57. Total          1     2     2     1     1     0     2     9 
58.  
59. （所影响的行数为 7 行） 
60. --*/ 

任意两个时间之间的星期几的次数-横.sql

1. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF')) 
2. drop function [dbo].[f_weekdaycount] 
3. GO 
4.  
5. /*--计算任意两个时间之间的星期几的次数(横向显示) 
6.  
7.     本方法直接判断 @@datefirst 做对应处理 
8.     不受 sp_language 及 set datefirst 的影响      
9.  
10. --邹建 2004.08(引用请保留此信息)--*/ 
11.  
12. /*--调用示例 
13.      
14.     select * from f_weekdaycount('2004-9-01','2004-9-02') 
15. --*/ 
16. create function f_weekdaycount( 
17. @dt_begin datetime, 
18. @dt_end datetime 
19. )returns table 
20. as 
21. return( 
22.     select 跨周数 
23.         ,周一=case a 
24.             when -1 then case when 1 between b and c then 1 else 0 end 
25.             when  0 then case when b<=1 then 1 else 0 end 
26.                     +case when c>=1 then 1 else 0 end 
27.             else a+case when b<=1 then 1 else 0 end 
28.                 +case when c>=1 then 1 else 0 end 
29.             end 
30.         ,周二=case a 
31.             when -1 then case when 2 between b and c then 1 else 0 end 
32.             when  0 then case when b<=2 then 1 else 0 end 
33.                     +case when c>=2 then 1 else 0 end 
34.             else a+case when b<=2 then 1 else 0 end 
35.                 +case when c>=2 then 1 else 0 end 
36.             end 
37.         ,周三=case a 
38.             when -1 then case when 3 between b and c then 1 else 0 end 
39.             when  0 then case when b<=3 then 1 else 0 end 
40.                     +case when c>=3 then 1 else 0 end 
41.             else a+case when b<=3 then 1 else 0 end 
42.                 +case when c>=3 then 1 else 0 end 
43.             end 
44.         ,周四=case a 
45.             when -1 then case when 4 between b and c then 1 else 0 end 
46.             when  0 then case when b<=4 then 1 else 0 end 
47.                     +case when c>=4 then 1 else 0 end 
48.             else a+case when b<=4 then 1 else 0 end 
49.                 +case when c>=4 then 1 else 0 end 
50.             end 
51.         ,周五=case a 
52.             when -1 then case when 5 between b and c then 1 else 0 end 
53.             when  0 then case when b<=5 then 1 else 0 end 
54.                     +case when c>=5 then 1 else 0 end 
55.             else a+case when b<=5 then 1 else 0 end 
56.                 +case when c>=5 then 1 else 0 end 
57.             end 
58.         ,周六=case a 
59.             when -1 then case when 6 between b and c then 1 else 0 end 
60.             when  0 then case when b<=6 then 1 else 0 end 
61.                     +case when c>=6 then 1 else 0 end 
62.             else a+case when b<=6 then 1 else 0 end 
63.                 +case when c>=6 then 1 else 0 end 
64.             end 
65.         ,周日=case a 
66.             when -1 then case when 0 between b and c then 1 else 0 end 
67.             when  0 then case when b<=0 then 1 else 0 end 
68.                     +case when c>=0 then 1 else 0 end 
69.             else a+case when b<=0 then 1 else 0 end 
70.                 +case when c>=0 then 1 else 0 end 
71.             end 
72.     from( 
73.         select 跨周数=case when @dt_begin<@dt_end 
74.                 then (datediff(day,@dt_begin,@dt_end)+7)/7 
75.                 else (datediff(day,@dt_end,@dt_begin)+7)/7 end 
76.             ,a=case when @dt_begin<@dt_end 
77.                 then datediff(week,@dt_begin,@dt_end)-1 
78.                 else datediff(week,@dt_end,@dt_begin)-1 end 
79.             ,b=case when @dt_begin<@dt_end 
80.                 then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 
81.                 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end 
82.             ,c=case when @dt_begin<@dt_end 
83.                 then (@@datefirst+datepart(weekday,@dt_end)-1)%7 
84.                 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a 
85. ) 
86. go 

统计--交叉表+日期+优先.sql

1. --交叉表,根据优先级取数据,日期处理 
2.  
3. 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) 
4. insert tb select 1,'A1','未订','08:00','09:00',1   ,5   ,null       ,null       ,1 
5. union all select 1,'A1','未订','09:00','10:00',1   ,5   ,null       ,null       ,1 
6. union all select 1,'A1','未订','10:00','11:00',1   ,5   ,null       ,null       ,1 
7. union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2  
8. --union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2 
9. union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2 
10. union all select 1,'A2','未订','08:00','09:00',1   ,5   ,null       ,null       ,1 
11. union all select 1,'A2','未订','09:00','10:00',1   ,5   ,null       ,null       ,1 
12. union all select 1,'A2','未订','10:00','11:00',1   ,5   ,null       ,null       ,1 
13. --union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 
14. union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2 
15. --union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2 
16. go 
17.  
18. /*--楼主这个问题要考虑几个方面 
19.  
20.     1. 取星期时,set datefirst 的影响 
21.     2. 优先级问题 
22.     3. qid,rid 应该是未知的(动态变化的) 
23. --*/ 
24.  
25. --实现的存储过程如下 
26. create proc p_qry 
27. @date smalldatetime --要查询的日期 
28. as 
29. set nocount on 
30. declare @week int,@s nvarchar(4000) 
31. --格式化日期和得到星期 
32. select @date=convert(char(10),@date,120) 
33.     ,@week=(@@datefirst+datepart(weekday,@date)-1)%7 
34.     ,@s='' 
35. select id=identity(int),* into #t 
36. from( 
37.     select top 100 percent 
38.         qid,rid,tagname, 
39.         starttime=convert(char(5),starttime,108), 
40.         endtime=convert(char(5),endtime,108) 
41.     from tb 
42.     where (@week between startweekday and endweekday) 
43.         or(@date between startdate and enddate) 
44.     order by qid,rid,starttime,d desc)a 
45.  
46. select @s=@s+N',['+rtrim(rid) 
47.     +N']=max(case when qid='+rtrim(qid) 
48.     +N' and rid=N'''+rtrim(rid) 
49.     +N''' then tagname else N'''' end)' 
50. from #t group by qid,rid 
51. exec(' 
52. select starttime,endtime'+@s+'  
53. from #t a 
54. where not exists( 
55.     select * from #t 
56.     where qid=a.qid and rid=a.rid  
57.         and starttime=a.starttime 
58.         and endtime=a.endtime 
59.         and id<a.id) 
60. group by starttime,endtime') 
61. go 
62.  
63. --调用 
64. exec p_qry '2005-1-17' 
65. exec p_qry '2005-1-18' 
66. go 
67.  
68. --删除测试 
69. drop table tb 
70. drop proc p_qry 
71.  
72. /*--测试结果 
73.  
74. starttime endtime A1         A2          
75. --------- ------- ---------- ----------  
76. 08:00     09:00   未订         未订 
77. 09:00     10:00   未订         未订 
78. 10:00     11:00   未订         未订 
79.  
80. starttime endtime A1         A2          
81. --------- ------- ---------- ----------  
82. 08:00     09:00   装修         未订 
83. 09:00     10:00   未订         装修 
84. 10:00     11:00   装修         未订 
85. --*/ 

各种字符串分拆处理函数.sql

1. --各种字符串分函数 
2.  
3. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) 
4. drop function [dbo].[f_splitSTR] 
5. GO 
6.  
7. --3.2.1 循环截取法 
8. CREATE FUNCTION f_splitSTR( 
9. @s   varchar(8000),   --待分拆的字符串 
10. @split varchar(10)     --数据分隔符 
11. )RETURNS @re TABLE(col varchar(100)) 
12. AS 
13. BEGIN 
14.     DECLARE @splitlen int 
15.     SET @splitlen=LEN(@split+'a')-2 
16.     WHILE CHARINDEX(@split,@s)>0 
17.     BEGIN 
18.         INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) 
19.         SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') 
20.     END 
21.     INSERT @re VALUES(@s) 
22.     RETURN 
23. END 
24. GO 
25.  
26.  
27. /*==============================================*/ 
28.  
29. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) 
30. drop function [dbo].[f_splitSTR] 
31. GO 
32.  
33. --3.2.3.1 使用临时性分拆辅助表法 
34. CREATE FUNCTION f_splitSTR( 
35. @s   varchar(8000),  --待分拆的字符串 
36. @split varchar(10)     --数据分隔符 
37. )RETURNS @re TABLE(col varchar(100)) 
38. AS 
39. BEGIN 
40.     --创建分拆处理的辅助表(用户定义函数中只能操作表变量) 
41.     DECLARE @t TABLE(ID int IDENTITY,b bit) 
42.     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b 
43.  
44.     INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) 
45.     FROM @t 
46.     WHERE ID<=LEN(@s+'a')  
47.         AND CHARINDEX(@split,@split+@s,ID)=ID 
48.     RETURN 
49. END 
50. GO 
51.  
52. /*==============================================*/ 
53.  
54. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) 
55. drop function [dbo].[f_splitSTR] 
56. GO 
57.  
58. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1) 
59. drop table [dbo].[tb_splitSTR] 
60. GO 
61.  
62. --3.2.3.2 使用永久性分拆辅助表法 
63. --字符串分拆辅助表 
64. SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR 
65. FROM syscolumns a,syscolumns b 
66. GO 
67.  
68. --字符串分拆处理函数 
69. CREATE FUNCTION f_splitSTR( 
70. @s     varchar(8000),  --待分拆的字符串 
71. @split  varchar(10)     --数据分隔符 
72. )RETURNS TABLE 
73. AS 
74. RETURN( 
75.     SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100)) 
76.     FROM tb_splitSTR 
77.     WHERE ID<=LEN(@s+'a')  
78.         AND CHARINDEX(@split,@split+@s,ID)=ID) 
79. GO 
80.  
81.  
82. /*==============================================*/ 
83.  
84. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) 
85. drop function [dbo].[f_splitSTR] 
86. GO 
87.  
88. --3.2.5 将数据项按数字与非数字再次拆份 
89. CREATE FUNCTION f_splitSTR( 
90. @s   varchar(8000),    --待分拆的字符串 
91. @split varchar(10)     --数据分隔符 
92. )RETURNS @re TABLE(No varchar(100),Value varchar(20)) 
93. AS 
94. BEGIN 
95.     --创建分拆处理的辅助表(用户定义函数中只能操作表变量) 
96.     DECLARE @t TABLE(ID int IDENTITY,b bit) 
97.     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b 
98.  
99.     INSERT @re  
100.     SELECT    No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')), 
101.         Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1)) 
102.     FROM( 
103.         SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)) 
104.         FROM @t 
105.         WHERE ID<=LEN(@s+'a')  
106.             AND CHARINDEX(@split,@split+@s,ID)=ID)a 
107.     RETURN 
108. END 
109. GO 
110.  
111.  
112. /*==============================================*/ 
113.  
114. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) 
115. drop function [dbo].[f_splitSTR] 
116. GO 
117.  
118. --3.2.6 分拆短信数据 
119. CREATE FUNCTION f_splitSTR(@s varchar(8000)) 
120. RETURNS @re TABLE(split varchar(10),value varchar(100)) 
121. AS 
122. BEGIN 
123.     DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split)) 
124.     INSERT @splits(split) 
125.     SELECT 'AC' UNION ALL 
126.     SELECT 'BC' UNION ALL 
127.     SELECT 'CC' UNION ALL 
128.     SELECT 'DC'     
129.     DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int 
130.     SELECT TOP 1  
131.         @pos1=1,@split=split,@splitlen=splitlen 
132.     FROM @splits 
133.     WHERE @s LIKE split+'%' 
134.     WHILE @pos1>0 
135.     BEGIN 
136.         SELECT TOP 1 
137.             @pos2=CHARINDEX(split,@s,@splitlen+1) 
138.         FROM @splits 
139.         WHERE CHARINDEX(split,@s,@splitlen+1)>0 
140.         ORDER BY CHARINDEX(split,@s,@splitlen+1) 
141.         IF @@ROWCOUNT=0 
142.         BEGIN 
143.             INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,'')) 
144.             RETURN 
145.         END 
146.         ELSE 
147.         BEGIN 
148.             INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1)) 
149.             SELECT TOP 1  
150.                 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'') 
151.             FROM @splits 
152.             WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%' 
153.         END 
154.     END 
155.     RETURN 
156. END 
157. GO 

各种字符串合并处理示例.sql

1. --各种字符串分函数 
2.  
3. --3.3.1 使用游标法进行字符串合并处理的示例。 
4. --处理的数据 
5. CREATE TABLE tb(col1 varchar(10),col2 int) 
6. INSERT tb SELECT 'a',1 
7. UNION ALL SELECT 'a',2 
8. UNION ALL SELECT 'b',1 
9. UNION ALL SELECT 'b',2 
10. UNION ALL SELECT 'b',3 
11.  
12. --合并处理 
13. --定义结果集表变量 
14. DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) 
15.  
16. --定义游标并进行合并处理 
17. DECLARE tb CURSOR LOCAL 
18. FOR 
19. SELECT col1,col2 FROM tb ORDER BY  col1,col2 
20. DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100) 
21. OPEN tb 
22. FETCH tb INTO @col1,@col2 
23. SELECT @col1_old=@col1,@s='' 
24. WHILE @@FETCH_STATUS=0 
25. BEGIN 
26.     IF @col1=@col1_old 
27.         SELECT @s=@s+','+CAST(@col2 as varchar) 
28.     ELSE 
29.     BEGIN 
30.         INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) 
31.         SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 
32.     END 
33.     FETCH tb INTO @col1,@col2 
34. END 
35. INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) 
36. CLOSE tb 
37. DEALLOCATE tb 
38. --显示结果并删除测试数据 
39. SELECT * FROM @t 
40. DROP TABLE tb 
41. /*--结果 
42. col1       col2 
43. ---------- ----------- 
44. a          1,2 
45. b          1,2,3 
46. --*/ 
47. GO 
48.  
49.  
50. /*==============================================*/ 
51.  
52.  
53. --3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例 
54. --处理的数据 
55. CREATE TABLE tb(col1 varchar(10),col2 int) 
56. INSERT tb SELECT 'a',1 
57. UNION ALL SELECT 'a',2 
58. UNION ALL SELECT 'b',1 
59. UNION ALL SELECT 'b',2 
60. UNION ALL SELECT 'b',3 
61. GO 
62.  
63. --合并处理函数 
64. CREATE FUNCTION dbo.f_str(@col1 varchar(10)) 
65. RETURNS varchar(100) 
66. AS 
67. BEGIN 
68.     DECLARE @re varchar(100) 
69.     SET @re='' 
70.     SELECT @re=@re+','+CAST(col2 as varchar) 
71.     FROM tb 
72.     WHERE col1=@col1 
73.     RETURN(STUFF(@re,1,1,'')) 
74. END 
75. GO 
76.  
77. --调用函数 
78. SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1 
79. --删除测试 
80. DROP TABLE tb 
81. DROP FUNCTION f_str 
82. /*--结果 
83. col1       col2 
84. ---------- ----------- 
85. a          1,2 
86. b          1,2,3 
87. --*/ 
88. GO 
89.  
90. /*==============================================*/ 
91.  
92.  
93. --3.3.3 使用临时表实现字符串合并处理的示例 
94. --处理的数据 
95. CREATE TABLE tb(col1 varchar(10),col2 int) 
96. INSERT tb SELECT 'a',1 
97. UNION ALL SELECT 'a',2 
98. UNION ALL SELECT 'b',1 
99. UNION ALL SELECT 'b',2 
100. UNION ALL SELECT 'b',3 
101.  
102. --合并处理 
103. SELECT col1,col2=CAST(col2 as varchar(100))  
104. INTO #t FROM tb 
105. ORDER BY col1,col2 
106. DECLARE @col1 varchar(10),@col2 varchar(100) 
107. UPDATE #t SET  
108.     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END, 
109.     @col1=col1, 
110.     col2=@col2 
111. SELECT * FROM #t 
112. /*--更新处理后的临时表 
113. col1       col2 
114. ---------- ------------- 
115. a          1 
116. a          1,2 
117. b          1 
118. b          1,2 
119. b          1,2,3 
120. --*/ 
121. --得到最终结果 
122. SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1 
123. /*--结果 
124. col1       col2 
125. ---------- ----------- 
126. a          1,2 
127. b          1,2,3 
128. --*/ 
129. --删除测试 
130. DROP TABLE tb,#t 
131. GO 
132.  
133.  
134. /*==============================================*/ 
135.  
136. --3.3.4.1 每组 <=2 条记录的合并 
137. --处理的数据 
138. CREATE TABLE tb(col1 varchar(10),col2 int) 
139. INSERT tb SELECT 'a',1 
140. UNION ALL SELECT 'a',2 
141. UNION ALL SELECT 'b',1 
142. UNION ALL SELECT 'b',2 
143. UNION ALL SELECT 'c',3 
144.  
145. --合并处理 
146. SELECT col1, 
147.     col2=CAST(MIN(col2) as varchar) 
148.         +CASE  
149.             WHEN COUNT(*)=1 THEN '' 
150.             ELSE ','+CAST(MAX(col2) as varchar) 
151.         END 
152. FROM tb 
153. GROUP BY col1 
154. DROP TABLE tb 
155. /*--结果 
156. col1       col2       
157. ---------- ---------- 
158. a          1,2 
159. b          1,2 
160. c          3 
161. --*/ 
162.  
163. --3.3.4.2 每组 <=3 条记录的合并 
164. --处理的数据 
165. CREATE TABLE tb(col1 varchar(10),col2 int) 
166. INSERT tb SELECT 'a',1 
167. UNION ALL SELECT 'a',2 
168. UNION ALL SELECT 'b',1 
169. UNION ALL SELECT 'b',2 
170. UNION ALL SELECT 'b',3 
171. UNION ALL SELECT 'c',3 
172.  
173. --合并处理 
174. SELECT col1, 
175.     col2=CAST(MIN(col2) as varchar) 
176.         +CASE  
177.             WHEN COUNT(*)=3 THEN ',' 
178.                 +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) 
179.             ELSE '' 
180.         END 
181.         +CASE  
182.             WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) 
183.             ELSE '' 
184.         END 
185. FROM tb a 
186. GROUP BY col1 
187. DROP TABLE tb 
188. /*--结果 
189. col1       col2 
190. ---------- ------------ 
191. a          1,2 
192. b          1,2,3 
193. c          3 
194. --*/ 
195. GO