断号查询

[sql]  view plain copy

1. --测试数据  
2. CREATE TABLE tb(col1 varchar(10),col2 int)  
3. INSERT tb SELECT 'a',2  
4. UNION ALL SELECT 'a',3  
5. UNION ALL SELECT 'a',6  
6. UNION ALL SELECT 'a',7  
7. UNION ALL SELECT 'a',8  
8. UNION ALL SELECT 'b',1  
9. UNION ALL SELECT 'b',5  
10. UNION ALL SELECT 'b',6  
11. UNION ALL SELECT 'b',7  
12. GO  
13.   
14. --缺号分布查询  
15. SELECT a.col1,start_col2=a.col2+1,  
16.     end_col2=(  
17.         SELECT MIN(col2) FROM tb aa  
18.         WHERE col1=a.col1 AND col2>a.col2   
19.             AND NOT EXISTS(  
20.                 SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))  
21.         -1  
22. FROM(  
23.     SELECT col1,col2 FROM tb  
24.     UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录  
25.     SELECT DISTINCT col1,0 FROM tb  
26. )a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b  
27. WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录  
28.     AND NOT EXISTS(  
29.         SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)  
30. ORDER BY a.col1,start_col2  
31. /*--结果  
32. col1       start_col2  end_col2      
33. -------------- -------------- -----------   
34. a          1           1  
35. a          4           5  
36. b          2           4  
37. --*/  
38.   
39.   
40. --测试数据  
41. CREATE TABLE tb(col1 varchar(10),col2 int)  
42. INSERT tb SELECT 'a',2  
43. UNION ALL SELECT 'a',3  
44. UNION ALL SELECT 'a',6  
45. UNION ALL SELECT 'a',7  
46. UNION ALL SELECT 'a',8  
47. UNION ALL SELECT 'b',1  
48. UNION ALL SELECT 'b',5  
49. UNION ALL SELECT 'b',6  
50. UNION ALL SELECT 'b',7  
51. GO  
52.   
53. --缺号分布查询  
54. SELECT a.col1,start_col2=a.col2+1,  
55.     end_col2=(  
56.         SELECT MIN(col2) FROM tb aa  
57.         WHERE col1=a.col1 AND col2>a.col2   
58.             AND NOT EXISTS(  
59.                 SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))  
60.         -1  
61. FROM(  
62.     SELECT col1,col2 FROM tb  
63.     UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录  
64.     SELECT DISTINCT col1,0 FROM tb  
65. )a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b  
66. WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录  
67.     AND NOT EXISTS(  
68.         SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)  
69. ORDER BY a.col1,start_col2  
70. /*--结果  
71. col1       start_col2  end_col2      
72. -------------- -------------- -----------   
73. a          1           1  
74. a          4           5  
75. b          2           4  
76. --*/  
77.    
78. 让你望见影子<trieagle@126.com>  9:49:01  
79. --生成已用编号分布字符串的函数  
80. CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))  
81. RETURNS varchar(8000)  
82. AS  
83. BEGIN  
84.     DECLARE @re varchar(8000),@pid int  
85.     SELECT @re='',@pid=-1  
86.     SELECT @re=CASE   
87.             WHEN col2=@pid+1 THEN @re  
88.             ELSE @re  
89.                 +CASE   
90.                     WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''  
91.                     ELSE CAST(-@pid as varchar)  
92.                 END  
93.                 +','+CAST(col2 as varchar)   
94.             END,  
95.         @pid=col2  
96.     FROM tb  
97.     WHERE col1=@col1  
98.     ORDER BY col2  
99.     RETURN(STUFF(@re,1,2,'')  
100.         +CASE   
101.             WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''  
102.             ELSE CAST(-@pid as varchar)  
103.         END)  
104. END  
105. GO  
106.   
107. --生成缺号分布字符串的函数  
108. CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))  
109. RETURNS VARCHAR(8000)  
110. AS  
111. BEGIN  
112.     DECLARE @re varchar(8000),@pid int  
113.     SELECT @re='',@pid=0  
114.     SELECT @re=CASE   
115.             WHEN col2=@pid+1 THEN @re  
116.             ELSE @re+','+CAST(@pid+1 as varchar)  
117.                 +CASE   
118.                     WHEN @pid+1=col2-1 THEN ''  
119.                     ELSE CAST(1-col2 as varchar)  
120.                 END  
121.             END,  
122.         @pid=col2  
123.     FROM tb  
124.     WHERE col1=@col1  
125.     ORDER BY col2  
126.     RETURN(STUFF(@re,1,1,''))  
127. END  
128. GO  
129.   
130. --调用测试  
131. --测试数据  
132. CREATE TABLE tb(col1 varchar(10),col2 int)  
133. INSERT tb SELECT 'a',2  
134. UNION ALL SELECT 'a',3  
135. UNION ALL SELECT 'a',5  
136. UNION ALL SELECT 'a',8  
137. UNION ALL SELECT 'a',9  
138. UNION ALL SELECT 'b',1  
139. UNION ALL SELECT 'b',5  
140. UNION ALL SELECT 'b',6  
141. UNION ALL SELECT 'b',7  
142.   
143. --查询  
144. SELECT col1,  
145.     col2_Series=dbo.f_GetStrSeries(col1),  
146.     col2_Series=dbo.f_GetStrNSeries(col1)  
147. FROM tb  
148. GROUP BY col1  
149. /*--结果  
150. col1       col2_Series       col2_Series   
151. -------------- ------------------------ --------------  
152. a          2-3,5,8-9        1,4,6-7  
153. b          1,5-7           2-4  
154. --*/