Digest SQL statements collected

  1. :
  2. SQL code
  3. — min server memory
  4. EXEC sp_configure N‘min server memory (MB)’, 0
  5. — max server memory
  6. EXEC sp_configure N‘max server memory (MB)’, 256
  7. RECONFIGURE WITH OVERRIDE
  8. .sql:
  9. SQL code
  10. /*–
  11.     SQL Server 2000 MyDB 
  12.     1?31
  13.     ALTER DATABASE?
  14.     ???
  15.     ?
  16. –*/
  17. — master
  18. USE master
  19. GO
  20. –A.  MyDB
  21. CREATE DATABASE MyDB
  22. ON PRIMARY                           —
  23.   ( NAME=‘MyDB_Primary’,
  24.    FILENAME= ‘c:/MyDB_Prm.mdf’),
  25. FILEGROUP MyDB_FG1                   –1
  26.   ( NAME = ‘MyDB_FG1_Dat1’,
  27.    FILENAME = ‘c:/MyDB_FG1_1.ndf’),  –1
  28.   ( NAME = ‘MyDB_FG1_Dat2’,
  29.    FILENAME = ‘d:/MyDB_FG1_2.ndf’),  –2
  30. FILEGROUP MyDB_FG2                   –2
  31.   ( NAME = ‘MyDB_FG1_Dat’,
  32.    FILENAME = ‘e:/MyDB_FG2.ndf’)     —
  33. LOG ON                               —
  34.   ( NAME=‘MyDB_log’,
  35.    FILENAME =‘d:/MyDB.ldf’)
  36. GO
  37. –B.
  38. ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT
  39. GO
  40. — MyDB
  41. USE MyDB
  42. –C. MyDB_FG1,MMyDB_FG2
  43. CREATE TABLE MyTable
  44.   ( cola   int   PRIMARY KEY ,
  45.     colb   char(8) ,
  46.     colc   image )
  47.     TEXTIMAGE_ON MyDB_FG2
  48. –MyDB_FG2
  49. CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2
  50. GO
  51. –D. MyDB_FG1_Dat1?MyDB_FG1_Dat1
  52. DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)
  53. –MyDB_FG1_Dat1
  54. ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1
  55. tempdb.sql:
  56. SQL code
  57. –A. tempdb10MB?
  58. ALTER DATABASE tempdb
  59. MODIFY FILE(
  60.     name=tempdev,
  61.     size=100 MB)
  62. GO
  63. –B. tempdb??
  64. ALTER DATABASE tempdb MODIFY FILE
  65.   ( NAME=‘tempdev’,
  66.    FILENAME =‘d:/tempdb.mdf’)
  67. ALTER DATABASE tempdb ADD FILE
  68.   ( NAME=‘tempdata_1’,
  69.    FILENAME =‘d:/tempdb_data_1.ndf’)
  70. .sql:
  71. SQL code
  72. –A.  datetime
  73. DECLARE @t TABLE(date char(21))
  74. INSERT @t SELECT ‘1900-1-1 00:00:00.000’
  75. INSERT @t SELECT ‘1900-1-1 00:00:00.001’
  76. INSERT @t SELECT ‘1900-1-1 00:00:00.009’
  77. INSERT @t SELECT ‘1900-1-1 00:00:00.002’
  78. INSERT @t SELECT ‘1900-1-1 00:00:00.003’
  79. INSERT @t SELECT ‘1900-1-1 00:00:00.004’
  80. INSERT @t SELECT ‘1900-1-1 00:00:00.005’
  81. INSERT @t SELECT ‘1900-1-1 00:00:00.006’
  82. INSERT @t SELECT ‘1900-1-1 00:00:00.007’
  83. INSERT @t SELECT ‘1900-1-1 00:00:00.008’
  84. SELECT date,=CAST(date as datetime) FROM @t
  85. /*–
  86. date                  
  87. ——————— ————————–
  88. 1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
  89. 1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
  90. 1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
  91. 1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
  92. 1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
  93. 1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
  94. 1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
  95. 1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
  96. 1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
  97. 1900-1-1 00:00:00.008 1900-01-01 00:00:00.007
  98. ? 10 ?
  99. –*/
  100. GO
  101. –B.  datetime
  102. DECLARE @dt datetime
  103. SET @dt=‘1900-1-2’
  104. SELECT CAST(@dt as binary(8))
  105. –: 0x0000000100000000
  106. SET @dt=’00:00:01′
  107. SELECT CAST(@dt as binary(8))
  108. –: 0x000000000000012C
  109. GO
  110. –C.  smalldatetime
  111. DECLARE @dt smalldatetime
  112. SET @dt=‘1900-1-2’
  113. SELECT CAST(@dt as binary(4))
  114. –: 0x00010000
  115. SET @dt=’00:10′
  116. SELECT CAST(@dt as binary(4))
  117. –: 0x0000000A
  118. ——————————————————————
  119. CONVERT.sql:
  120. SQL code
  121. –,Style
  122. –1. Style=101,:mm/dd/yyyy
  123. SELECT CONVERT(datetime,’11/1/2003′,101)
  124. –:2003-11-01 00:00:00.000
  125. –2. Style=101,:dd/mm/yyyy
  126. SELECT CONVERT(datetime,’11/1/2003′,103)
  127. –:2003-01-11 00:00:00.000
  128. /*==  ==*/
  129. DECLARE @dt datetime
  130. SET @dt=‘2003-1-11’
  131. –1. Style=101,:mm/dd/yyyy
  132. SELECT CONVERT(varchar,@dt,101)
  133. –:01/11/2003
  134. –2. Style=103,:dd/mm/yyyy
  135. SELECT CONVERT(varchar,@dt,103)
  136. –:11/01/2003
  137. /*== ,style ==*/
  138. SELECT CONVERT(varchar,‘2003-1-11’,101)
  139. –:2003-1-11
  140. SET DATEFORMAT.sql
  141. SQL code
  142. –1.
  143. /*–
  144.     SET DATEFORMATCONVERT
  145.     styleCONVERT?
  146. –*/
  147. — ??CONVERTstyle?SET DATAFORMAT?CONVERTstyle?style?
  148. — //
  149. SET DATEFORMAT DMY
  150. –StyleCONVERTSET DATEFORMAT
  151. SELECT CONVERT(datetime,‘2-1-2005’)
  152. –: 2005-01-02 00:00:00.000
  153. –StyleCONVERTSET DATEFORMAT
  154. SELECT CONVERT(datetime,‘2-1-2005’,101)
  155. –: 2005-02-01 00:00:00.000
  156. GO
  157. –2.
  158. /*–
  159.     ?
  160.     SET DATEFORMAT?
  161. –*/
  162. –??SET DATEFORMAT???
  163. DECLARE @dt datetime
  164. –SET DATEFORMAT:
  165. SET DATEFORMAT MDY
  166. SET @dt=’01-2002-03′
  167. SELECT @dt
  168. –: 2002-01-03 00:00:00.000
  169. SET @dt=’01-02-03′
  170. SELECT @dt
  171. –: 2003-01-02 00:00:00.000
  172. GO
  173. –3.
  174. /*–
  175.     ?SQL Server
  176.     SET DATEFORMAT?
  177. –*/
  178. –???SET DATEFORMAT??
  179. DECLARE @dt datetime
  180. –SET DATEFORMAT:
  181. SET DATEFORMAT MDY
  182. SET @dt=‘010203’
  183. SELECT @dt
  184. –: 2001-02-03 00:00:00.000
  185. –SET DATEFORMAT:
  186. SET DATEFORMAT DMY
  187. SET @dt=‘010203’
  188. SELECT @dt
  189. –: 2001-02-03 00:00:00.000
  190. SET @dt=’01-02-03′
  191. SELECT @dt
  192. –: 2003-02-01 00:00:00.000
  193. SET LANGUAGE.sql
  194. SQL code
  195. –(SET LANGUAGE)?DATENAMECONVERT?
  196. USE master
  197. –: English
  198. SET LANGUAGE N‘English’
  199. SELECT
  200.     DATENAME(Month,GETDATE()) AS [Month],
  201.     DATENAME(Weekday,GETDATE()) AS [Weekday],
  202.     CONVERT(varchar,GETDATE(),109) AS [CONVERT]
  203. /*–:
  204. Month    Weekday   CONVERT
  205. ————- ————– ——————————-
  206. March    Tuesday   Mar 15 2005  8:59PM
  207. –*/
  208. –:
  209. SET LANGUAGE N
  210. SELECT
  211.     DATENAME(Month,GETDATE()) AS [Month],
  212.     DATENAME(Weekday,GETDATE()) AS [Weekday],
  213.     CONVERT(varchar,GETDATE(),109) AS [CONVERT]
  214. /*–
  215. Month    Weekday    CONVERT
  216. ————- ————— —————————————–
  217. 05            05 19 2005  2:49:20:607PM
  218. –*/
  219. .sql
  220. SQL code
  221. DECLARE @dt datetime
  222. SET @dt=GETDATE()
  223. –1??yyyy-m-d
  224. SELECT REPLACE(CONVERT(varchar(10),@dt,120),N‘-0’,‘-‘)
  225. –2??yyyymmdd
  226. –A. 1
  227. SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N),8,0,N)+N
  228. –B. 2
  229. SELECT DATENAME(Year,@dt)+N+DATENAME(Month,@dt)+N+DATENAME(Day,@dt)+N
  230. –3??yyyymd
  231. SELECT DATENAME(Year,@dt)+N+CAST(DATEPART(Month,@dt) AS varchar)+N+DATENAME(Day,@dt)+N
  232. –4.+?yyyy-mm-dd hh:mi:ss:mmm
  233. SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
  234. .sql
  235. SQL code
  236. DECLARE @dt datetime
  237. SET @dt=GETDATE()
  238. DECLARE @number int
  239. SET @number=3
  240. –1?
  241. –A.
  242. SELECT CONVERT(char(5),@dt,120)+‘1-1’
  243. –B.
  244. SELECT CONVERT(char(5),@dt,120)+’12-31′
  245. –2?
  246. –A.
  247. SELECT CONVERT(datetime,
  248.     CONVERT(char(8),
  249.         DATEADD(Month,
  250.             DATEPART(Quarter,@dt)*3-Month(@dt)-2,
  251.             @dt),
  252.         120)+‘1’)
  253. –B. ?CASE?
  254. SELECT CONVERT(datetime,
  255.     CONVERT(char(8),
  256.         DATEADD(Month,
  257.             DATEPART(Quarter,@dt)*3-Month(@dt),
  258.             @dt),
  259.         120)
  260.     +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
  261.         THEN ’31’ELSE ’30’ END)
  262. –C. ??
  263. SELECT DATEADD(Day,-1,
  264.     CONVERT(char(8),
  265.         DATEADD(Month,
  266.             1+DATEPART(Quarter,@dt)*3-Month(@dt),
  267.             @dt),
  268.         120)+‘1’)
  269. –3?
  270. –A.
  271. SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+‘1’)
  272. –B.
  273. SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+‘1’)
  274. –C. ??
  275. SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
  276. –4?
  277. SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
  278. –5?
  279. –A.  1
  280. SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
  281. –B.  1
  282. SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
  283. —————————————————————
  284. .sql
  285. SQL code
  286. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_DateADD]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  287.     drop function [dbo].[f_DateADD]
  288. GO
  289. /*–
  290.     ?DATEADD?
  291.     ?
  292.     ???
  293.     2005311?13112?
  294.     ?DATEADD?
  295.     ?
  296.     y-m-d h:m:s.m | -y-m-d h:m:s.m
  297.     ?
  298.     ?
  299.     ?-????
  300.     ???
  301. –*/
  302. /*–
  303.     SELECT dbo.f_DateADD(GETDATE(),’11:10′)
  304. –*/
  305. CREATE FUNCTION dbo.f_DateADD(
  306. @Date     datetime,
  307. @DateStr   varchar(23)
  308. )RETURNS datetime
  309. AS
  310. BEGIN
  311.     DECLARE @bz int,@s varchar(12),@i int
  312.     IF @DateStr IS NULL OR @Date IS NULL
  313.         OR(CHARINDEX(‘.’,@DateStr)>0
  314.             AND @DateStr NOT LIKE ‘%[:]%[:]%.%’)
  315.         RETURN(NULL)
  316.     IF @DateStr= RETURN(@Date)
  317.     SELECT @bz=CASE
  318.             WHEN LEFT(@DateStr,1)=‘-‘ THEN -1
  319.             ELSE 1 END,
  320.         @DateStr=CASE
  321.             WHEN LEFT(@Date,1)=‘-‘
  322.             THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,)
  323.             ELSE RTRIM(LTRIM(@DateStr)) END
  324.     IF CHARINDEX(‘ ‘,@DateStr)>1
  325.         OR CHARINDEX(‘-‘,@DateStr)>1
  326.         OR(CHARINDEX(‘.’,@DateStr)=0
  327.             AND CHARINDEX(‘:’,@DateStr)=0)
  328.     BEGIN
  329.         SELECT @i=CHARINDEX(‘ ‘,@DateStr+‘ ‘)
  330.             ,@s=REVERSE(LEFT(@DateStr,@i-1))+‘-‘
  331.             ,@DateStr=STUFF(@DateStr,1,@i,)
  332.             ,@i=0
  333.         WHILE @s> and @i<3
  334.             SELECT @Date=CASE @i
  335.                     WHEN 0 THEN DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX(‘-‘,@s)-1)),@Date)
  336.                     WHEN 1 THEN DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX(‘-‘,@s)-1)),@Date)
  337.                     WHEN 2 THEN DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX(‘-‘,@s)-1)),@Date)
  338.                 END,
  339.                 @s=STUFF(@s,1,CHARINDEX(‘-‘,@s),),
  340.                 @i=@i+1
  341.     END
  342.     IF @DateStr>
  343.     BEGIN
  344.         IF CHARINDEX(‘.’,@DateStr)>0
  345.             SELECT @Date=DATEADD(Millisecond
  346.                     ,@bz*STUFF(@DateStr,1,CHARINDEX(‘.’,@DateStr),),
  347.                     @Date),
  348.                 @DateStr=LEFT(@DateStr,CHARINDEX(‘.’,@DateStr)-1)+‘:’,
  349.                 @i=0
  350.         ELSE
  351.             SELECT @DateStr=@DateStr+‘:’,@i=0
  352.         WHILE @DateStr> and @i<3
  353.             SELECT @Date=CASE @i
  354.                     WHEN 0 THEN DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(‘:’,@DateStr)-1),@Date)
  355.                     WHEN 1 THEN DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(‘:’,@DateStr)-1),@Date)
  356.                     WHEN 2 THEN DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(‘:’,@DateStr)-1),@Date)
  357.                 END,
  358.                 @DateStr=STUFF(@DateStr,1,CHARINDEX(‘:’,@DateStr),),
  359.                 @i=@i+1
  360.     END
  361.     RETURN(@Date)
  362. END
  363. GO
  364. .sql
  365. SQL code
  366. DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
  367. INSERT @t SELECT 1,‘aa’,‘1999-01-01’
  368. UNION ALL SELECT 2,‘bb’,‘1996-02-29’
  369. UNION ALL SELECT 3,‘bb’,‘1934-03-01’
  370. UNION ALL SELECT 4,‘bb’,‘1966-04-01’
  371. UNION ALL SELECT 5,‘bb’,‘1997-05-01’
  372. UNION ALL SELECT 6,‘bb’,‘1922-11-21’
  373. UNION ALL SELECT 7,‘bb’,‘1989-12-11’
  374. DECLARE @dt1 datetime,@dt2 datetime
  375. — 2003-12-05  2004-02-28
  376. SELECT @dt1=‘2003-12-05’,@dt2=‘2004-02-28’
  377. SELECT * FROM @t
  378. WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
  379.         BETWEEN @dt1 AND @dt2
  380.     OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
  381.         BETWEEN @dt1 AND @dt2
  382. /*–
  383. ID         Name       Birthday
  384. —————- —————- ————————–
  385. 1           aa         1999-01-01 00:00:00.000
  386. 7           bb         1989-12-11 00:00:00.000
  387. –*/
  388. — 2003-12-05  2006-02-28
  389. SET @dt2=‘2006-02-28’
  390. SELECT * FROM @t
  391. WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
  392.         BETWEEN @dt1 AND @dt2
  393.     OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
  394.         BETWEEN @dt1 AND @dt2
  395. /*–
  396. ID         Name       Birthday
  397. —————- —————– ————————–
  398. 1           aa         1999-01-01 00:00:00.000
  399. 2           bb         1996-02-29 00:00:00.000
  400. 7           bb         1989-12-11 00:00:00.000
  401. –*/
  402. .sql
  403. SQL code
  404. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_getdate]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  405. drop function [dbo].[f_getdate]
  406. GO
  407. /*–
  408.     
  409.     /
  410. — 2003.12()–*/
  411. /*–
  412.     — 2003 
  413.     SELECT * FROM dbo.f_getdate(2003,0)
  414.     
  415.     — 2003 
  416.     SELECT * FROM dbo.f_getdate(2003,1)
  417.     — 2003 
  418.     SELECT * FROM dbo.f_getdate(2003,NULL)
  419. –*/
  420. CREATE FUNCTION dbo.f_getdate(
  421. @year int,    —
  422. @bz bit       –@bz=0 ,@bz=1 ,@bz IS NULL
  423. )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
  424. AS
  425. BEGIN
  426.     DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime)
  427.     INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,‘1900-1-1’)
  428.     FROM sysobjects a ,sysobjects b
  429.     UPDATE @tb SET Date=DATEADD(DAY,id,Date)
  430.     DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,‘1900-12-31’)
  431.     IF @bz=0
  432.         INSERT INTO @re(Date,Weekday)
  433.         SELECT Date,DATENAME(Weekday,Date)
  434.         FROM @tb
  435.         WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
  436.     ELSE IF @bz=1
  437.         INSERT INTO @re(Date,Weekday)
  438.         SELECT Date,DATENAME(Weekday,Date)
  439.         FROM @tb
  440.         WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 IN (0,6)
  441.     ELSE
  442.         INSERT INTO @re(Date,Weekday)
  443.         SELECT Date,DATENAME(Weekday,Date)
  444.         FROM @tb
  445.     RETURN
  446. END
  447. GO
  448. /*====================================================================*/
  449. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_getdate]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  450. drop function [dbo].[f_getdate]
  451. GO
  452. /*–
  453.     
  454. — 2005.03()–*/
  455. /*–
  456.     —
  457.     SELECT * FROM dbo.f_getdate(‘2005-1-3′,’2005-4-5’,0)
  458.     
  459.     —
  460.     SELECT * FROM dbo.f_getdate(‘2005-1-3′,’2005-4-5’,1)
  461.     
  462.     —
  463.     SELECT * FROM dbo.f_getdate(‘2005-1-3′,’2005-4-5’,NULL)
  464. –*/
  465. CREATE FUNCTION dbo.f_getdate(
  466. @begin_date Datetime,  —
  467. @end_date Datetime,    —
  468. @bz bit                –@bz=0 ,@bz=1 ,@bz IS NULL
  469. )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
  470. AS
  471. BEGIN
  472.     DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
  473.     INSERT INTO @tb(a) SELECT TOP 366 0
  474.     FROM sysobjects a ,sysobjects b
  475.     IF @bz=0
  476.         WHILE @begin_date<=@end_date
  477.         BEGIN
  478.             INSERT INTO @re(Date,Weekday)
  479.             SELECT Date,DATENAME(Weekday,Date)
  480.             FROM(
  481.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  482.                 FROM @tb
  483.             )a WHERE Date<=@end_date
  484.                 AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
  485.             SET @begin_date=DATEADD(Day,366,@begin_date)
  486.         END
  487.     ELSE IF @bz=1
  488.         WHILE @begin_date<=@end_date
  489.         BEGIN
  490.             INSERT INTO @re(Date,Weekday)
  491.             SELECT Date,DATENAME(Weekday,Date)
  492.             FROM(
  493.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  494.                 FROM @tb
  495.             )a WHERE Date<=@end_date
  496.                 AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
  497.             SET @begin_date=DATEADD(Day,366,@begin_date)
  498.         END
  499.     ELSE
  500.         WHILE @begin_date<=@end_date
  501.         BEGIN
  502.             INSERT INTO @re(Date,Weekday)
  503.             SELECT Date,DATENAME(Weekday,Date)
  504.             FROM(
  505.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  506.                 FROM @tb
  507.             )a WHERE Date<=@end_date
  508.             SET @begin_date=DATEADD(Day,366,@begin_date)
  509.         END
  510.     RETURN
  511. END
  512. GO
  513. ————————-
  514. ().sql
  515. SQL code
  516. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_WorkDay]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  517. drop function [dbo].[f_WorkDay]
  518. GO
  519. CREATE FUNCTION f_WorkDay(
  520. @dt_begin datetime,  —
  521. @dt_end  datetime    —
  522. )RETURNS int
  523. AS
  524. BEGIN
  525.     DECLARE @workday int,@i int,@bz bit,@dt datetime
  526.     IF @dt_begin>@dt_end
  527.         SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
  528.     ELSE
  529.         SET @bz=0
  530.     SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
  531.         @workday=@i/7*5,
  532.         @dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
  533.     WHILE @dt_begin<=@dt_end
  534.     BEGIN
  535.         SELECT @workday=CASE
  536.             WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
  537.             THEN @workday+1 ELSE @workday END,
  538.             @dt_begin=@dt_begin+1
  539.     END
  540.     RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
  541. END
  542. GO
  543. /*=================================================================*/
  544. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_WorkDayADD]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  545. drop function [dbo].[f_WorkDayADD]
  546. GO
  547. –,
  548. CREATE FUNCTION f_WorkDayADD(
  549. @date    datetime,  —
  550. @workday int       —
  551. )RETURNS datetime
  552. AS
  553. BEGIN
  554.     DECLARE @bz int
  555.     —
  556.     SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
  557.         ,@date=DATEADD(Week,@workday/5,@date)
  558.         ,@workday=@workday%5
  559.     —
  560.     WHILE @workday<>0
  561.         SELECT @date=DATEADD(Day,@bz,@date),
  562.             @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
  563.                 THEN @workday-@bz ELSE @workday END
  564.     —
  565.     WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)
  566.         SET @date=DATEADD(Day,@bz,@date)
  567.     RETURN(@date)
  568. END
  569. ().sql
  570. SQL code
  571. if exists (select * from dbo.sysobjects where id = object_id(N‘[tb_Holiday]’) and OBJECTPROPERTY(id, N‘IsUserTable’) = 1)
  572. drop table [tb_Holiday]
  573. GO
  574. CREATE TABLE tb_Holiday(
  575. HDate smalldatetime primary key clustered, —
  576. Name nvarchar(50) not null)             —
  577. GO
  578. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_WorkDay]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  579. drop function [dbo].[f_WorkDay]
  580. GO
  581. CREATE FUNCTION f_WorkDay(
  582. @dt_begin datetime,  —
  583. @dt_end  datetime   —
  584. )RETURNS int
  585. AS
  586. BEGIN
  587.     IF @dt_begin>@dt_end
  588.         RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
  589.             +1-(
  590.                 SELECT COUNT(*) FROM tb_Holiday
  591.                 WHERE HDate BETWEEN @dt_begin AND @dt_end))
  592.     RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
  593.         +1-(
  594.             SELECT COUNT(*) FROM tb_Holiday
  595.             WHERE HDate BETWEEN @dt_end AND @dt_begin)))
  596. END
  597. GO
  598. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_WorkDayADD]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  599. drop function [dbo].[f_WorkDayADD]
  600. GO
  601. CREATE FUNCTION f_WorkDayADD(
  602. @date    datetime,  —
  603. @workday int       —
  604. )RETURNS datetime
  605. AS
  606. BEGIN
  607.     IF @workday>0
  608.         WHILE @workday>0
  609.             SELECT @date=@date+@workday,@workday=count(*)
  610.             FROM tb_Holiday
  611.             WHERE HDate BETWEEN @date AND @date+@workday
  612.     ELSE
  613.         WHILE @workday<0
  614.             SELECT @date=@date+@workday,@workday=-count(*)
  615.             FROM tb_Holiday
  616.             WHERE HDate BETWEEN @date AND @date+@workday
  617.     RETURN(@date)
  618. END
  619. .sql
  620. SQL code
  621. if exists (select * from dbo.sysobjects where id = object_id(N‘[tb_worktime]’) and OBJECTPROPERTY(id, N‘IsUserTable’) = 1)
  622. drop table [tb_worktime]
  623. GO
  624. CREATE TABLE tb_worktime(
  625.     ID       int identity(1,1) PRIMARY KEY,            —
  626.     time_start smalldatetime,                            —
  627.     time_end  smalldatetime,                           —
  628.     worktime  AS DATEDIFF(Minute,time_start,time_end)  –()
  629. )
  630. GO
  631. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_WorkTime]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  632. drop function [dbo].[f_WorkTime]
  633. GO
  634. CREATE FUNCTION f_WorkTime(
  635. @date_begin datetime,  —
  636. @date_end datetime     —
  637. )RETURNS int
  638. AS
  639. BEGIN
  640.     DECLARE @worktime int
  641.     IF DATEDIFF(Day,@date_begin,@date_end)=0
  642.         SELECT @worktime=SUM(DATEDIFF(Minute,
  643.             CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
  644.                 THEN CONVERT(VARCHAR,@date_begin,108)
  645.                 ELSE time_start END,
  646.             CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end
  647.                 THEN CONVERT(VARCHAR,@date_end,108)
  648.                 ELSE time_end END))
  649.         FROM tb_worktime
  650.         WHERE time_end>CONVERT(VARCHAR,@date_begin,108)
  651.             AND time_start<CONVERT(VARCHAR,@date_end,108)
  652.     ELSE
  653.         SET @worktime
  654.             =(SELECT SUM(CASE
  655.                     WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
  656.                     THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end)
  657.                     ELSE worktime END)
  658.                 FROM tb_worktime
  659.                 WHERE time_end>CONVERT(VARCHAR,@date_begin,108))
  660.             +(SELECT SUM(CASE
  661.                     WHEN CONVERT(VARCHAR,@date_end,108)<time_end
  662.                     THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108))
  663.                     ELSE worktime END)
  664.                 FROM tb_worktime
  665.                 WHERE time_start<CONVERT(VARCHAR,@date_end,108))
  666.             +CASE
  667.                 WHEN DATEDIFF(Day,@date_begin,@date_end)>1
  668.                 THEN (DATEDIFF(Day,@date_begin,@date_end)-1)
  669.                     *(SELECT SUM(worktime) FROM tb_worktime)
  670.                 ELSE 0 END
  671.     RETURN(@worktime)
  672. END
  673. .sql
  674. SQL code
  675. declare @T1 table( int, varchar(10), datetime, datetime)
  676. insert into @T1
  677.     select 12,‘1’,‘2003/04/01’,‘2004/05/01’
  678.     union all select 22,‘2’,‘2001/02/01’,‘2003/02/01’
  679.     union all select 42,‘3’,‘2000/04/01’,‘2003/05/01’
  680.     union all select 25,‘5’,‘2003/04/01’,‘2003/05/01’
  681. declare @NB table( int, varchar(10), int)
  682. insert into @NB
  683.     select 12,‘1’,2003
  684.     union all select 12,‘1’,2004
  685.     union all select 22,‘2’,2001
  686.     union all select 22,‘2’,2002
  687.     union all select 22,‘2’,2003
  688. declare @YB table( int, varchar(10), int, varchar(2))
  689. insert into @YB
  690.     select 12,‘1’,2003,’04’
  691.     union all select 22,‘2’,2001,’01’
  692.     union all select 22,‘2’,2001,’12’
  693. –+
  694. select top 8246 y=identity(int,1753,1)
  695. into #tby from
  696.     (select id from syscolumns) a,
  697.     (select id from syscolumns) b,
  698.     (select id from syscolumns) c
  699. select top 12 m=identity(int,1,1)
  700. into #tbm from syscolumns
  701. /*—-*/
  702. select a.*
  703. from(
  704. select a.,a.,=b.y
  705. from @T1 a,#tby b
  706. where b.y between year() and year()
  707. ) a left join @NB b on a.=b. and a.=b.
  708. where b. is null
  709. select a.*
  710. from(
  711. select a.,a.,=b.y,=right(’00’+cast(c.m as varchar),2)
  712. from @T1 a,#tby b,#tbm c
  713. where b.y*100+c.m between convert(varchar(6),,112)
  714.     and convert(varchar(6),,112)
  715. ) a left join @YB b on a.=b. and a.=b. and a.=b.
  716. where b. is null
  717. order by a.,a.,a.,a.
  718. drop table #tby,#tbm
  719. .sql
  720. SQL code
  721. create table tb(ID int,Time datetime)
  722. insert tb select 1,‘2005/01/24 16:20’
  723. union all select 2,‘2005/01/23 22:45’
  724. union all select 3,‘2005/01/23 0:30’
  725. union all select 4,‘2005/01/21 4:28’
  726. union all select 5,‘2005/01/20 13:22’
  727. union all select 6,‘2005/01/19 20:30’
  728. union all select 7,‘2005/01/19 18:23’
  729. union all select 8,‘2005/01/18 9:14’
  730. union all select 9,‘2005/01/18 18:04’
  731. go
  732. –?
  733. select     case when grouping(b.Time)=1 then ‘Total’ else b.Time end,
  734.     [Mon]=sum(case a.week when 1 then 1 else 0 end),
  735.     [Tue]=sum(case a.week when 2 then 1 else 0 end),
  736.     [Wed]=sum(case a.week when 3 then 1 else 0 end),
  737.     [Thu]=sum(case a.week when 4 then 1 else 0 end),
  738.     [Fri]=sum(case a.week when 5 then 1 else 0 end),
  739.     [Sat]=sum(case a.week when 6 then 1 else 0 end),
  740.     [Sun]=sum(case a.week when 0 then 1 else 0 end),
  741.     [Total]=count(a.week)
  742. from(
  743.     select Time=convert(char(5),dateadd(hour,-1,Time),108)
  744.             –1am,1?
  745.         ,week=(@@datefirst+datepart(weekday,Time)-1)%7
  746.             –@@datefirstdatepart
  747.     from tb
  748. )a right join(
  749.     select id=1,a=’16:00′,b=’19:59′,Time=‘[5pm – 9pm)’ union all
  750.     select id=2,a=’20:00′,b=’23:59′,Time=‘[9pm – 1am)’ union all
  751.     select id=3,a=’00:00′,b=’02:59′,Time=‘[1am – 4am)’ union all
  752.     select id=4,a=’03:00′,b=’07:29′,Time=‘[4am – 8:30am)’ union all
  753.     select id=5,a=’07:30′,b=’11:59′,Time=‘[8:30am – 1pm)’ union all
  754.     select id=6,a=’12:00′,b=’15:59′,Time=‘[1pm – 5pm)’
  755. )b on a.Time>=b.a and a.Time<b.b
  756. group by b.id,b.Time with rollup
  757. having grouping(b.Time)=0 or grouping(b.id)=1
  758. go
  759. drop table tb
  760. /*–
  761.                Mon   Tue   Wed   Thu   Fri   Sat   Sun   Total 
  762. ————– —– —– —– —– —– —— —- ——-
  763. [5pm – 9pm)    0     1     2     0     0     0     0     3
  764. [9pm – 1am)    0     0     0     0     0     0     2     2
  765. [1am – 4am)    0     0     0     0     0     0     0     0
  766. [4am – 8:30am) 0     0     0     0     1     0     0     1
  767. [8:30am – 1pm) 0     1     0     0     0     0     0     1
  768. [1pm – 5pm)    1     0     0     1     0     0     0     2
  769. Total          1     2     2     1     1     0     2     9
  770. ? 7 ?
  771. –*/
  772. -.sql
  773. SQL code
  774. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_weekdaycount]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  775. drop function [dbo].[f_weekdaycount]
  776. GO
  777. /*–()
  778.      @@datefirst 
  779.      sp_language  set datefirst      
  780. — 2004.08()–*/
  781. /*–
  782.     
  783.     select * from f_weekdaycount(‘2004-9-01′,’2004-9-02’)
  784. –*/
  785. create function f_weekdaycount(
  786. @dt_begin datetime,
  787. @dt_end datetime
  788. )returns table
  789. as
  790. return(
  791.     select
  792.         ,=case a
  793.             when -1 then case when 1 between b and c then 1 else 0 end
  794.             when  0 then case when b<=1 then 1 else 0 end
  795.                     +case when c>=1 then 1 else 0 end
  796.             else a+case when b<=1 then 1 else 0 end
  797.                 +case when c>=1 then 1 else 0 end
  798.             end
  799.         ,=case a
  800.             when -1 then case when 2 between b and c then 1 else 0 end
  801.             when  0 then case when b<=2 then 1 else 0 end
  802.                     +case when c>=2 then 1 else 0 end
  803.             else a+case when b<=2 then 1 else 0 end
  804.                 +case when c>=2 then 1 else 0 end
  805.             end
  806.         ,=case a
  807.             when -1 then case when 3 between b and c then 1 else 0 end
  808.             when  0 then case when b<=3 then 1 else 0 end
  809.                     +case when c>=3 then 1 else 0 end
  810.             else a+case when b<=3 then 1 else 0 end
  811.                 +case when c>=3 then 1 else 0 end
  812.             end
  813.         ,=case a
  814.             when -1 then case when 4 between b and c then 1 else 0 end
  815.             when  0 then case when b<=4 then 1 else 0 end
  816.                     +case when c>=4 then 1 else 0 end
  817.             else a+case when b<=4 then 1 else 0 end
  818.                 +case when c>=4 then 1 else 0 end
  819.             end
  820.         ,=case a
  821.             when -1 then case when 5 between b and c then 1 else 0 end
  822.             when  0 then case when b<=5 then 1 else 0 end
  823.                     +case when c>=5 then 1 else 0 end
  824.             else a+case when b<=5 then 1 else 0 end
  825.                 +case when c>=5 then 1 else 0 end
  826.             end
  827.         ,=case a
  828.             when -1 then case when 6 between b and c then 1 else 0 end
  829.             when  0 then case when b<=6 then 1 else 0 end
  830.                     +case when c>=6 then 1 else 0 end
  831.             else a+case when b<=6 then 1 else 0 end
  832.                 +case when c>=6 then 1 else 0 end
  833.             end
  834.         ,=case a
  835.             when -1 then case when 0 between b and c then 1 else 0 end
  836.             when  0 then case when b<=0 then 1 else 0 end
  837.                     +case when c>=0 then 1 else 0 end
  838.             else a+case when b<=0 then 1 else 0 end
  839.                 +case when c>=0 then 1 else 0 end
  840.             end
  841.     from(
  842.         select =case when @dt_begin<@dt_end
  843.                 then (datediff(day,@dt_begin,@dt_end)+7)/7
  844.                 else (datediff(day,@dt_end,@dt_begin)+7)/7 end
  845.             ,a=case when @dt_begin<@dt_end
  846.                 then datediff(week,@dt_begin,@dt_end)-1
  847.                 else datediff(week,@dt_end,@dt_begin)-1 end
  848.             ,b=case when @dt_begin<@dt_end
  849.                 then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
  850.                 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
  851.             ,c=case when @dt_begin<@dt_end
  852.                 then (@@datefirst+datepart(weekday,@dt_end)-1)%7
  853.                 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
  854. )
  855. go
  856. —————————————-
  857. –++.sql
  858. SQL code
  859. –,,
  860. 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)
  861. insert tb select 1,‘A1’,,’08:00′,’09:00′,1   ,5   ,null       ,null       ,1
  862. union all select 1,‘A1’,,’09:00′,’10:00′,1   ,5   ,null       ,null       ,1
  863. union all select 1,‘A1’,,’10:00′,’11:00′,1   ,5   ,null       ,null       ,1
  864. union all select 1,‘A1’,,’08:00′,’09:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  865. –union all select 1,‘A1’,,’09:00′,’10:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  866. union all select 1,‘A1’,,’10:00′,’11:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  867. union all select 1,‘A2’,,’08:00′,’09:00′,1   ,5   ,null       ,null       ,1
  868. union all select 1,‘A2’,,’09:00′,’10:00′,1   ,5   ,null       ,null       ,1
  869. union all select 1,‘A2’,,’10:00′,’11:00′,1   ,5   ,null       ,null       ,1
  870. –union all select 1,‘A2’,,’08:00′,’09:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  871. union all select 1,‘A2’,,’09:00′,’10:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  872. –union all select 1,‘A2’,,’10:00′,’11:00′,null,null,‘2005-1-18’,‘2005-1-19’,2
  873. go
  874. /*–
  875.     1. ,set datefirst 
  876.     2. 
  877.     3. qid,rid ()
  878. –*/
  879. create proc p_qry
  880. @date smalldatetime —
  881. as
  882. set nocount on
  883. declare @week int,@s nvarchar(4000)
  884. select @date=convert(char(10),@date,120)
  885.     ,@week=(@@datefirst+datepart(weekday,@date)-1)%7
  886.     ,@s=
  887. select id=identity(int),* into #t
  888. from(
  889.     select top 100 percent
  890.         qid,rid,tagname,
  891.         starttime=convert(char(5),starttime,108),
  892.         endtime=convert(char(5),endtime,108)
  893.     from tb
  894.     where (@week between startweekday and endweekday)
  895.         or(@date between startdate and enddate)
  896.     order by qid,rid,starttime,d desc)a
  897. select @s=@s+N‘,[‘+rtrim(rid)
  898.     +N‘]=max(case when qid=’+rtrim(qid)
  899.     +N‘ and rid=N’+rtrim(rid)
  900.     +N‘ then tagname else N’‘ end)’
  901. from #t group by qid,rid
  902. exec(‘
  903. select starttime,endtime‘+@s+’
  904. from #t a
  905. where not exists(
  906.     select * from #t
  907.     where qid=a.qid and rid=a.rid
  908.         and starttime=a.starttime
  909.         and endtime=a.endtime
  910.         and id<a.id)
  911. group by starttime,endtime’)
  912. go
  913. exec p_qry ‘2005-1-17’
  914. exec p_qry ‘2005-1-18’
  915. go
  916. drop table tb
  917. drop proc p_qry
  918. /*–
  919. starttime endtime A1         A2         
  920. ——— ——- ———- ———- 
  921. 08:00     09:00            
  922. 09:00     10:00            
  923. 10:00     11:00            
  924. starttime endtime A1         A2         
  925. ——— ——- ———- ———- 
  926. 08:00     09:00            
  927. 09:00     10:00            
  928. 10:00     11:00            
  929. –*/
  930. .sql
  931. SQL code
  932. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_splitSTR]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  933. drop function [dbo].[f_splitSTR]
  934. GO
  935. –3.2.1
  936. CREATE FUNCTION f_splitSTR(
  937. @s   varchar(8000),   —
  938. @split varchar(10)     —
  939. )RETURNS @re TABLE(col varchar(100))
  940. AS
  941. BEGIN
  942.     DECLARE @splitlen int
  943.     SET @splitlen=LEN(@split+‘a’)-2
  944.     WHILE CHARINDEX(@split,@s)>0
  945.     BEGIN
  946.         INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  947.         SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,)
  948.     END
  949.     INSERT @re VALUES(@s)
  950.     RETURN
  951. END
  952. GO
  953. /*==============================================*/
  954. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_splitSTR]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  955. drop function [dbo].[f_splitSTR]
  956. GO
  957. –3.2.3.1
  958. CREATE FUNCTION f_splitSTR(
  959. @s   varchar(8000),  —
  960. @split varchar(10)     —
  961. )RETURNS @re TABLE(col varchar(100))
  962. AS
  963. BEGIN
  964.     –()
  965.     DECLARE @t TABLE(ID int IDENTITY,b bit)
  966.     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
  967.     INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
  968.     FROM @t
  969.     WHERE ID<=LEN(@s+‘a’)
  970.         AND CHARINDEX(@split,@split+@s,ID)=ID
  971.     RETURN
  972. END
  973. GO
  974. /*==============================================*/
  975. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_splitSTR]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  976. drop function [dbo].[f_splitSTR]
  977. GO
  978. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[tb_splitSTR]’) and objectproperty(id,N‘IsUserTable’)=1)
  979. drop table [dbo].[tb_splitSTR]
  980. GO
  981. –3.2.3.2
  982. SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
  983. FROM syscolumns a,syscolumns b
  984. GO
  985. CREATE FUNCTION f_splitSTR(
  986. @s     varchar(8000),  —
  987. @split  varchar(10)     —
  988. )RETURNS TABLE
  989. AS
  990. RETURN(
  991.     SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
  992.     FROM tb_splitSTR
  993.     WHERE ID<=LEN(@s+‘a’)
  994.         AND CHARINDEX(@split,@split+@s,ID)=ID)
  995. GO
  996. /*==============================================*/
  997. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_splitSTR]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  998. drop function [dbo].[f_splitSTR]
  999. GO
  1000. –3.2.5
  1001. CREATE FUNCTION f_splitSTR(
  1002. @s   varchar(8000),    —
  1003. @split varchar(10)     —
  1004. )RETURNS @re TABLE(No varchar(100),Value varchar(20))
  1005. AS
  1006. BEGIN
  1007.     –()
  1008.     DECLARE @t TABLE(ID int IDENTITY,b bit)
  1009.     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
  1010.     INSERT @re
  1011.     SELECT    No=REVERSE(STUFF(col,1,PATINDEX(‘%[^-^.^0-9]%’,col+‘a’)-1,)),
  1012.         Value=REVERSE(LEFT(col,PATINDEX(‘%[^-^.^0-9]%’,col+‘a’)-1))
  1013.     FROM(
  1014.         SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
  1015.         FROM @t
  1016.         WHERE ID<=LEN(@s+‘a’)
  1017.             AND CHARINDEX(@split,@split+@s,ID)=ID)a
  1018.     RETURN
  1019. END
  1020. GO
  1021. /*==============================================*/
  1022. if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[f_splitSTR]’) and xtype in (N‘FN’, N‘IF’, N‘TF’))
  1023. drop function [dbo].[f_splitSTR]
  1024. GO
  1025. –3.2.6
  1026. CREATE FUNCTION f_splitSTR(@s varchar(8000))
  1027. RETURNS @re TABLE(split varchar(10),value varchar(100))
  1028. AS
  1029. BEGIN
  1030.     DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
  1031.     INSERT @splits(split)
  1032.     SELECT ‘AC’ UNION ALL
  1033.     SELECT ‘BC’ UNION ALL
  1034.     SELECT ‘CC’ UNION ALL
  1035.     SELECT ‘DC’
  1036.     DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
  1037.     SELECT TOP 1
  1038.         @pos1=1,@split=split,@splitlen=splitlen
  1039.     FROM @splits
  1040.     WHERE @s LIKE split+‘%’
  1041.     WHILE @pos1>0
  1042.     BEGIN
  1043.         SELECT TOP 1
  1044.             @pos2=CHARINDEX(split,@s,@splitlen+1)
  1045.         FROM @splits
  1046.         WHERE CHARINDEX(split,@s,@splitlen+1)>0
  1047.         ORDER BY CHARINDEX(split,@s,@splitlen+1)
  1048.         IF @@ROWCOUNT=0
  1049.         BEGIN
  1050.             INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,))
  1051.             RETURN
  1052.         END
  1053.         ELSE
  1054.         BEGIN
  1055.             INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
  1056.             SELECT TOP 1
  1057.                 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,)
  1058.             FROM @splits
  1059.             WHERE STUFF(@s,1,@pos2-1,) LIKE split+‘%’
  1060.         END
  1061.     END
  1062.     RETURN
  1063. END
  1064. GO
  1065. ———————————————
  1066. .sql
  1067. SQL code
  1068. –3.3.1 ?
  1069. CREATE TABLE tb(col1 varchar(10),col2 int)
  1070. INSERT tb SELECT ‘a’,1
  1071. UNION ALL SELECT ‘a’,2
  1072. UNION ALL SELECT ‘b’,1
  1073. UNION ALL SELECT ‘b’,2
  1074. UNION ALL SELECT ‘b’,3
  1075. DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
  1076. DECLARE tb CURSOR LOCAL
  1077. FOR
  1078. SELECT col1,col2 FROM tb ORDER BY  col1,col2
  1079. DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
  1080. OPEN tb
  1081. FETCH tb INTO @col1,@col2
  1082. SELECT @col1_old=@col1,@s=
  1083. WHILE @@FETCH_STATUS=0
  1084. BEGIN
  1085.     IF @col1=@col1_old
  1086.         SELECT @s=@s+‘,’+CAST(@col2 as varchar)
  1087.     ELSE
  1088.     BEGIN
  1089.         INSERT @t VALUES(@col1_old,STUFF(@s,1,1,))
  1090.         SELECT @s=‘,’+CAST(@col2 as varchar),@col1_old=@col1
  1091.     END
  1092.     FETCH tb INTO @col1,@col2
  1093. END
  1094. INSERT @t VALUES(@col1_old,STUFF(@s,1,1,))
  1095. CLOSE tb
  1096. DEALLOCATE tb
  1097. SELECT * FROM @t
  1098. DROP TABLE tb
  1099. /*–
  1100. col1       col2
  1101. ———- ———–
  1102. a          1,2
  1103. b          1,2,3
  1104. –*/
  1105. GO
  1106. /*==============================================*/
  1107. –3.3.2 ?SELECT
  1108. CREATE TABLE tb(col1 varchar(10),col2 int)
  1109. INSERT tb SELECT ‘a’,1
  1110. UNION ALL SELECT ‘a’,2
  1111. UNION ALL SELECT ‘b’,1
  1112. UNION ALL SELECT ‘b’,2
  1113. UNION ALL SELECT ‘b’,3
  1114. GO
  1115. CREATE FUNCTION dbo.f_str(@col1 varchar(10))
  1116. RETURNS varchar(100)
  1117. AS
  1118. BEGIN
  1119.     DECLARE @re varchar(100)
  1120.     SET @re=
  1121.     SELECT @re=@re+‘,’+CAST(col2 as varchar)
  1122.     FROM tb
  1123.     WHERE col1=@col1
  1124.     RETURN(STUFF(@re,1,1,))
  1125. END
  1126. GO
  1127. SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
  1128. DROP TABLE tb
  1129. DROP FUNCTION f_str
  1130. /*–
  1131. col1       col2
  1132. ———- ———–
  1133. a          1,2
  1134. b          1,2,3
  1135. –*/
  1136. GO
  1137. /*==============================================*/
  1138. –3.3.3
  1139. CREATE TABLE tb(col1 varchar(10),col2 int)
  1140. INSERT tb SELECT ‘a’,1
  1141. UNION ALL SELECT ‘a’,2
  1142. UNION ALL SELECT ‘b’,1
  1143. UNION ALL SELECT ‘b’,2
  1144. UNION ALL SELECT ‘b’,3
  1145. SELECT col1,col2=CAST(col2 as varchar(100))
  1146. INTO #t FROM tb
  1147. ORDER BY col1,col2
  1148. DECLARE @col1 varchar(10),@col2 varchar(100)
  1149. UPDATE #t SET
  1150.     @col2=CASE WHEN @col1=col1 THEN @col2+‘,’+col2 ELSE col2 END,
  1151.     @col1=col1,
  1152.     col2=@col2
  1153. SELECT * FROM #t
  1154. /*–
  1155. col1       col2
  1156. ———- ————-
  1157. a          1
  1158. a          1,2
  1159. b          1
  1160. b          1,2
  1161. b          1,2,3
  1162. –*/
  1163. SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
  1164. /*–
  1165. col1       col2
  1166. ———- ———–
  1167. a          1,2
  1168. b          1,2,3
  1169. –*/
  1170. DROP TABLE tb,#t
  1171. GO
  1172. /*==============================================*/
  1173. –3.3.4.1  <=2
  1174. CREATE TABLE tb(col1 varchar(10),col2 int)
  1175. INSERT tb SELECT ‘a’,1
  1176. UNION ALL SELECT ‘a’,2
  1177. UNION ALL SELECT ‘b’,1
  1178. UNION ALL SELECT ‘b’,2
  1179. UNION ALL SELECT ‘c’,3
  1180. SELECT col1,
  1181.     col2=CAST(MIN(col2) as varchar)
  1182.         +CASE
  1183.             WHEN COUNT(*)=1 THEN 
  1184.             ELSE ‘,’+CAST(MAX(col2) as varchar)
  1185.         END
  1186. FROM tb
  1187. GROUP BY col1
  1188. DROP TABLE tb
  1189. /*–
  1190. col1       col2      
  1191. ———- ———-
  1192. a          1,2
  1193. b          1,2
  1194. c          3
  1195. –*/
  1196. –3.3.4.2  <=3
  1197. CREATE TABLE tb(col1 varchar(10),col2 int)
  1198. INSERT tb SELECT ‘a’,1
  1199. UNION ALL SELECT ‘a’,2
  1200. UNION ALL SELECT ‘b’,1
  1201. UNION ALL SELECT ‘b’,2
  1202. UNION ALL SELECT ‘b’,3
  1203. UNION ALL SELECT ‘c’,3
  1204. SELECT col1,
  1205.     col2=CAST(MIN(col2) as varchar)
  1206.         +CASE
  1207.             WHEN COUNT(*)=3 THEN ‘,’
  1208.                 +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
  1209.             ELSE 
  1210.         END
  1211.         +CASE
  1212.             WHEN COUNT(*)>=2 THEN ‘,’+CAST(MAX(col2) as varchar)
  1213.             ELSE 
  1214.         END
  1215. FROM tb a
  1216. GROUP BY col1
  1217. DROP TABLE tb
  1218. /*–
  1219. col1       col2
  1220. ———- ————
  1221. a          1,2
  1222. b          1,2,3
  1223. c          3
  1224. –*/
  1225. GO