一个值得研究学习的SQL
1
if
2exists
3(
4select *
5from dbo.sysobjects
6where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
7)
8drop table [dbo].[t]
9GO
10
11CREATE TABLE [dbo].[t] (
12 [id] [int] IDENTITY (1, 1) NOT NULL ,
13 [lid] [nvarchar] (50) ,
14 [name] [nvarchar] (50) ,
15 [type] [int] NOT NULL
16)
17GO
18
19
20
21insert into t (lid,[name],[type])
22select '11','城站火车站',0
23union all select '11','葵巷建国路口',0
24union all select '11','菜市桥',0
25union all select '11','潮鸣寺巷',0
26union all select '11','宝善桥建国路口',0
27union all select '11','宝善桥',0
28union all select '11','市体育馆',0
29union all select '11','武林广场',0
30union all select '11','武林门',0
31union all select '11','武林们马塍路口',0
32union all select '11','八字桥',0
33union all select '11','浙大西溪校区',0
34union all select '11','庆丰村',0
35union all select '11','教工路口',0
36union all select '11','花园新村',0
37union all select '11','浙江工商大学',0
38union all select '11','电子科技大学',0
39union all select '11','翠苑新村',0
40union all select '57','大关小区',0
41union all select '57','通信市场',0
42union all select '57','德胜新村',0
43union all select '57','潮王路口',0
44union all select '57','朝晖五区',0
45union all select '57','朝晖三区',0
46union all select '57','西湖文化广场东',0
47union all select '57','武林广场',0
48union all select '57','武林小广场',0
49union all select '57','半道红',0
50union all select '57','文三路口',0
51union all select '57','上宁桥',0
52union all select '57','花园新村',0
53union all select '57','浙江工商大学',0
54union all select '57','文一路口',0
55union all select '57','教工路北口',0
56union all select '57','大关桥西',0
57union all select '57','上塘路口',0
58union all select '57','大关西六苑',0
59union all select '57','香积寺路口',0
60union all select '57','大关小区',0
61union all select '14','武林小广场',0
62union all select '14','昌化新村',0
63union all select '14','长寿桥',0
64union all select '14','延安路口',0
65union all select '14','中大广场',0
66union all select '14','众安桥',0
67union all select '14','浙一医院',0
68union all select '14','大学路北口',0
69union all select '14','庆春门',0
70union all select '14','金衙庄',0
71union all select '14','总管塘',0
72union all select '14','华东家具市场',0
73union all select '14','近江村',0
74union all select '14','汽车南站',0
75union all select '14','汽车南站',1
76union all select '14','近江村',1
77union all select '14','华东家具市场',1
78union all select '14','总管塘',1
79union all select '14','金衙庄',1
80union all select '14','庆春门',1
81union all select '14','大学路北口',1
82union all select '14','浙一医院',1
83union all select '14','众安桥',1
84union all select '14','中大广场',1
85union all select '14','延安路口',1
86union all select '14','长寿桥',1
87union all select '14','昌化新村',1
88union all select '14','武林小广场',1
89union all select 'K105','火车东站',0
90union all select 'K105','汽车东站',0
91union all select 'K105','严家弄',0
92union all select 'K105','景芳五区',0
93union all select 'K105','景御路口',0
94union all select 'K105','庆春东路',0
95union all select 'K105','采荷新村',0
96union all select 'K105','观音塘小区',0
97union all select 'K105','总管塘',0
98union all select 'K105','章家桥',0
99union all select 'K105','浙二医院',0
100union all select 'K105','官巷口',0
101union all select 'K105','湖滨',0
102union all select 'K105','胜利剧院',0
103union all select 'K105','孩儿巷',0
104union all select 'K105','延安新村',0
105union all select 'K105','武林小广场',0
106union all select 'K105','杭州大厦',0
107union all select 'K105','中北桥',0
108union all select 'K105','施家桥',0
109union all select 'K105','建国北路文晖路口',0
110union all select 'K105','文晖大桥东',0
111union all select 'K105','机神村',0
112union all select 'K105','天城路口',0
113union all select 'K105','新塘路口',0
114union all select 'K105','火车东站',0
115union all select '39','闸口',0
116union all select '39','水澄桥',0
117union all select '39','海月桥',0
118union all select '39','美政桥',0
119union all select '39','复兴路北口',0
120union all select '39','三廊庙',0
121union all select '39','木材新村',0
122union all select '39','二凉亭',0
123union all select '39','望江门外',0
124union all select '39','汽车南站',0
125union all select '39','近江村',0
126union all select '39','华东家具市场',0
127union all select '39','总管塘',0
128union all select '39','城站火车站',0
129union all select 'K101','城站火车站',0
130union all select 'K101','总管塘',0
131union all select 'K101','观音塘小区',0
132union all select 'K101','采荷新村',0
133union all select 'K101','红菱新村',0
134union all select 'K101','凤起东路口',0
135union all select 'K101','双菱路北口',0
136union all select 'K101','市红会医院',0
137union all select 'K101','建国路口',0
138union all select 'K101','新华路口',0
139union all select 'K101','中北路口',0
140union all select 'K101','延安路口',0
141union all select 'K101','浙大湖滨校区',0
142union all select 'K101','昌化新村',0
143union all select 'K101','市府大楼',0
144union all select 'K101','武林门马塍路口',0
145union all select 'K101','八字桥',0
146union all select 'K101','浙大西溪校区',0
147union all select 'K101','庆丰村',0
148union all select 'K101','玉古路天目山路口',0
149union all select 'K101','西湖体育馆',0
150union all select '21/K21','城站火车站',0
151union all select '21/K21','章家桥',0
152union all select '21/K21','新城隧道东口',0
153union all select '21/K21','解放路秋涛路口',0
154union all select '21/K21','采荷新村',0
155union all select '21/K21','红菱新村',0
156union all select '21/K21','双菱路北口',0
157union all select '21/K21','市红会医院',0
158union all select '21/K21','建国路口',0
159union all select '21/K21','新华路口',0
160union all select '21/K21','中北路口',0
161union all select '21/K21','延安路口',0
162union all select '21/K21','浙大湖滨校区',0
163union all select '21/K21','昌化新村',0
164union all select '21/K21','市府大楼',0
165union all select '21/K21','武林门马塍路口',0
166union all select '21/K21','八字桥',0
167union all select '21/K21','浙大西溪校区',0
168union all select '21/K21','庆丰村',0
169union all select '21/K21','跑马场',0
170union all select '21/K21','黄龙体育中心',0
171union all select '21/K21','浙大附中',0
172union all select '21/K21','求是路',0
173union all select '21/K21','西湖体育馆',0
174union all select '58/K58','大关小区',0
175union all select '58/K58','上塘路香积寺路口',0
176union all select '58/K58','大关西六苑',0
177union all select '58/K58','上塘路口',0
178union all select '58/K58','大关桥西',0
179union all select '58/K58','教工路北口',0
180union all select '58/K58','文一路口',0
181union all select '58/K58','浙江工商大学',0
182union all select '58/K58','花园新村',0
183union all select '58/K58','上宁桥',0
184union all select '58/K58','文三新村',0
185union all select '58/K58','八字桥',0
186union all select '58/K58','武林门马塍路口',0
187union all select '58/K58','武林小广场',0
188union all select '58/K58','武林广场',0
189union all select '58/K58','中北桥',0
190union all select '58/K58','朝晖一区',0
191union all select '58/K58','朝晖三区',0
192union all select '58/K58','朝晖五区',0
193union all select '58/K58','潮王路口',0
194union all select '58/K58','德胜新村',0
195union all select '58/K58','通信市场',0
196union all select '58/K58','大关小区',0
197union all select 'K101','西湖体育馆',1
198union all select 'K101','玉古路天目山路口',1
199union all select 'K101','庆丰村',1
200union all select 'K101','浙大西溪校区',1
201union all select 'K101','八字桥',1
202union all select 'K101','武林门马塍路口',1
203union all select 'K101','市府大楼',1
204union all select 'K101','昌化新村',1
205union all select 'K101','浙大湖滨校区',1
206union all select 'K101','延安路口',1
207union all select 'K101','中北路口',1
208union all select 'K101','新华路口',1
209union all select 'K101','建国路口',1
210union all select 'K101','市红会医院',1
211union all select 'K101','双菱路北口',1
212union all select 'K101','凤起东路口',1
213union all select 'K101','红菱新村',1
214union all select 'K101','采荷新村',1
215union all select 'K101','观音塘小区',1
216union all select 'K101','总管塘',1
217union all select 'K101','城站火车站',1
218
219
220
221
222
/****** Object: Stored Procedure dbo.Search Script Date: 2005-9-8 10:28:35 ******/
223if
224exists
225(
226select *
227from dbo.sysobjects
228where id = object_id(N'[dbo].[Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1
229)
230drop procedure [dbo].[Search]
231
232GO
233
234SET QUOTED_IDENTIFIER OFF
235GO
236SET ANSI_NULLS ON
237GO
238
239
240
241/****** Object: Stored Procedure dbo.Search Script Date: 2005-9-8 10:28:35 ******/
242CREATE proc Search
243@name1 nvarchar(50),
244@name2 nvarchar(50)
245as
246
247--中转站
248create table #tmp
249(
250 tmp_id int identity(1,1),
251 tmp_name NVARCHAR(50)
252)
253
254--站点队列
255create table #tmp1
256(
257 tmp1_id int identity(1,1),
258 tmp1_name NVARCHAR(50)
259)
260
261
262--查找结果
263create table #result
264(
265 r_id int,
266 r_lid nvarchar(50),
267 r_name nvarchar(50),
268 r_type int
269)
270
271--直达
272insert into #result
273select c.* from t a,t b,t c where
274a.lid=b.lid and a.[type]=b.[type] and a.id<b.id
275and a.[name] = @name1 and b.[name] = @name2
276and c.id>=a.id and c.id<=b.id order by c.id
277
278if @@rowcount>0 begin
279 select * from #result
280end
281else begin
282 --换车
283 DECLARE @CurrenName NVARCHAR(50)
284 SET @CurrenName = @name1
285 change:
286 /*
287
--车次入栈
288 insert into #tmp (tmp_lid)
289 select distinct lid from t where [name] = @CurrenName
290 DECLARE @CurrenBus NVARCHAR(50)
291 SELECT TOP 1 @CurrenBus = tmp_lid FROM #tmp
292
*/
293 INSERT INTO #tmp1 (tmp1_name)
294 SELECT DISTINCT b.[name] FROM t a,t b WHERE a.[name] = @CurrenName AND b.lid = a.lid AND b.[name] <> @CurrenName
295
296 INSERT INTO #tmp (tmp_name)
297 select d.[tmp1_name] from t a,t b,t c, #tmp1 d where
298 a.lid=b.lid and a.[type]=b.[type] and a.id<b.id
299 and a.[name] = d.[tmp1_name] and b.[name] = @name2
300 and c.id>=a.id and c.id<=b.id
301
302 IF @@rowcount>0 BEGIN
303 select distinct c.* from t a,t b,t c,#tmp d where
304 a.lid=b.lid and a.[type]=b.[type] and a.id<b.id
305 and a.[name] = @name1 and b.[name] = d.tmp_name
306 and c.id>=a.id and c.id<=b.id order by c.id
307
308 select distinct c.* from t a,t b,t c,#tmp d where
309 a.lid=b.lid and a.[type]=b.[type] and a.id<b.id
310 and a.[name] = d.tmp_name and b.[name] = @name2
311 and c.id>=a.id and c.id<=b.id order by c.id
312 END
313 --SELECT * FROM #tmp
314end
315
316drop table #result
317drop table #tmp1
318drop table #tmp
319GO
320
321SET QUOTED_IDENTIFIER OFF
322GO
323SET ANSI_NULLS ON
324GO
325
326exec search '文一路口','总管塘'
327
328
329/*交集操作*/
330select distinct(lid) from t where type=0 and
331lid in
332(
333select distinct(lid) from t where type=1
334)
335/*并操作*/
336select distinct(lid) from t where type=0
337union
338select distinct(lid) from t where type=1
339/*差操作*/
340select distinct(lid) from t where type=0 and
341lid not in
342(
343select distinct(lid) from t where type=1
344)
345
346
347
348
349
350
351
if 2
exists 3
(4
select * 5
from dbo.sysobjects 6
where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 17
)8
drop table [dbo].[t]9
GO10
11
CREATE TABLE [dbo].[t] (12
[id] [int] IDENTITY (1, 1) NOT NULL ,13
[lid] [nvarchar] (50) ,14
[name] [nvarchar] (50) ,15
[type] [int] NOT NULL 16
) 17
GO18
19
20
21
insert into t (lid,[name],[type])22
select '11','城站火车站',023
union all select '11','葵巷建国路口',024
union all select '11','菜市桥',025
union all select '11','潮鸣寺巷',026
union all select '11','宝善桥建国路口',027
union all select '11','宝善桥',028
union all select '11','市体育馆',029
union all select '11','武林广场',030
union all select '11','武林门',031
union all select '11','武林们马塍路口',032
union all select '11','八字桥',033
union all select '11','浙大西溪校区',034
union all select '11','庆丰村',035
union all select '11','教工路口',036
union all select '11','花园新村',037
union all select '11','浙江工商大学',038
union all select '11','电子科技大学',039
union all select '11','翠苑新村',040
union all select '57','大关小区',041
union all select '57','通信市场',042
union all select '57','德胜新村',043
union all select '57','潮王路口',044
union all select '57','朝晖五区',045
union all select '57','朝晖三区',046
union all select '57','西湖文化广场东',047
union all select '57','武林广场',048
union all select '57','武林小广场',049
union all select '57','半道红',050
union all select '57','文三路口',051
union all select '57','上宁桥',052
union all select '57','花园新村',053
union all select '57','浙江工商大学',054
union all select '57','文一路口',055
union all select '57','教工路北口',056
union all select '57','大关桥西',057
union all select '57','上塘路口',058
union all select '57','大关西六苑',059
union all select '57','香积寺路口',060
union all select '57','大关小区',061
union all select '14','武林小广场',062
union all select '14','昌化新村',063
union all select '14','长寿桥',064
union all select '14','延安路口',065
union all select '14','中大广场',066
union all select '14','众安桥',067
union all select '14','浙一医院',068
union all select '14','大学路北口',069
union all select '14','庆春门',070
union all select '14','金衙庄',071
union all select '14','总管塘',072
union all select '14','华东家具市场',073
union all select '14','近江村',074
union all select '14','汽车南站',075
union all select '14','汽车南站',176
union all select '14','近江村',177
union all select '14','华东家具市场',178
union all select '14','总管塘',179
union all select '14','金衙庄',180
union all select '14','庆春门',181
union all select '14','大学路北口',182
union all select '14','浙一医院',183
union all select '14','众安桥',184
union all select '14','中大广场',185
union all select '14','延安路口',186
union all select '14','长寿桥',187
union all select '14','昌化新村',188
union all select '14','武林小广场',189
union all select 'K105','火车东站',090
union all select 'K105','汽车东站',091
union all select 'K105','严家弄',092
union all select 'K105','景芳五区',093
union all select 'K105','景御路口',094
union all select 'K105','庆春东路',095
union all select 'K105','采荷新村',096
union all select 'K105','观音塘小区',097
union all select 'K105','总管塘',098
union all select 'K105','章家桥',099
union all select 'K105','浙二医院',0100
union all select 'K105','官巷口',0101
union all select 'K105','湖滨',0102
union all select 'K105','胜利剧院',0103
union all select 'K105','孩儿巷',0104
union all select 'K105','延安新村',0105
union all select 'K105','武林小广场',0106
union all select 'K105','杭州大厦',0107
union all select 'K105','中北桥',0108
union all select 'K105','施家桥',0109
union all select 'K105','建国北路文晖路口',0110
union all select 'K105','文晖大桥东',0111
union all select 'K105','机神村',0112
union all select 'K105','天城路口',0113
union all select 'K105','新塘路口',0114
union all select 'K105','火车东站',0115
union all select '39','闸口',0116
union all select '39','水澄桥',0117
union all select '39','海月桥',0118
union all select '39','美政桥',0119
union all select '39','复兴路北口',0120
union all select '39','三廊庙',0121
union all select '39','木材新村',0122
union all select '39','二凉亭',0123
union all select '39','望江门外',0124
union all select '39','汽车南站',0125
union all select '39','近江村',0126
union all select '39','华东家具市场',0127
union all select '39','总管塘',0128
union all select '39','城站火车站',0129
union all select 'K101','城站火车站',0130
union all select 'K101','总管塘',0131
union all select 'K101','观音塘小区',0132
union all select 'K101','采荷新村',0133
union all select 'K101','红菱新村',0134
union all select 'K101','凤起东路口',0135
union all select 'K101','双菱路北口',0136
union all select 'K101','市红会医院',0137
union all select 'K101','建国路口',0138
union all select 'K101','新华路口',0139
union all select 'K101','中北路口',0140
union all select 'K101','延安路口',0141
union all select 'K101','浙大湖滨校区',0142
union all select 'K101','昌化新村',0143
union all select 'K101','市府大楼',0144
union all select 'K101','武林门马塍路口',0145
union all select 'K101','八字桥',0146
union all select 'K101','浙大西溪校区',0147
union all select 'K101','庆丰村',0148
union all select 'K101','玉古路天目山路口',0149
union all select 'K101','西湖体育馆',0150
union all select '21/K21','城站火车站',0151
union all select '21/K21','章家桥',0152
union all select '21/K21','新城隧道东口',0153
union all select '21/K21','解放路秋涛路口',0154
union all select '21/K21','采荷新村',0155
union all select '21/K21','红菱新村',0156
union all select '21/K21','双菱路北口',0157
union all select '21/K21','市红会医院',0158
union all select '21/K21','建国路口',0159
union all select '21/K21','新华路口',0160
union all select '21/K21','中北路口',0161
union all select '21/K21','延安路口',0162
union all select '21/K21','浙大湖滨校区',0163
union all select '21/K21','昌化新村',0164
union all select '21/K21','市府大楼',0165
union all select '21/K21','武林门马塍路口',0166
union all select '21/K21','八字桥',0167
union all select '21/K21','浙大西溪校区',0168
union all select '21/K21','庆丰村',0169
union all select '21/K21','跑马场',0170
union all select '21/K21','黄龙体育中心',0171
union all select '21/K21','浙大附中',0172
union all select '21/K21','求是路',0173
union all select '21/K21','西湖体育馆',0174
union all select '58/K58','大关小区',0175
union all select '58/K58','上塘路香积寺路口',0176
union all select '58/K58','大关西六苑',0177
union all select '58/K58','上塘路口',0178
union all select '58/K58','大关桥西',0179
union all select '58/K58','教工路北口',0180
union all select '58/K58','文一路口',0181
union all select '58/K58','浙江工商大学',0182
union all select '58/K58','花园新村',0183
union all select '58/K58','上宁桥',0184
union all select '58/K58','文三新村',0185
union all select '58/K58','八字桥',0186
union all select '58/K58','武林门马塍路口',0187
union all select '58/K58','武林小广场',0188
union all select '58/K58','武林广场',0189
union all select '58/K58','中北桥',0190
union all select '58/K58','朝晖一区',0191
union all select '58/K58','朝晖三区',0192
union all select '58/K58','朝晖五区',0193
union all select '58/K58','潮王路口',0194
union all select '58/K58','德胜新村',0195
union all select '58/K58','通信市场',0196
union all select '58/K58','大关小区',0197
union all select 'K101','西湖体育馆',1198
union all select 'K101','玉古路天目山路口',1199
union all select 'K101','庆丰村',1200
union all select 'K101','浙大西溪校区',1201
union all select 'K101','八字桥',1202
union all select 'K101','武林门马塍路口',1203
union all select 'K101','市府大楼',1204
union all select 'K101','昌化新村',1205
union all select 'K101','浙大湖滨校区',1206
union all select 'K101','延安路口',1207
union all select 'K101','中北路口',1208
union all select 'K101','新华路口',1209
union all select 'K101','建国路口',1210
union all select 'K101','市红会医院',1211
union all select 'K101','双菱路北口',1212
union all select 'K101','凤起东路口',1213
union all select 'K101','红菱新村',1214
union all select 'K101','采荷新村',1215
union all select 'K101','观音塘小区',1216
union all select 'K101','总管塘',1217
union all select 'K101','城站火车站',1218
219
220
221
222
/****** Object: Stored Procedure dbo.Search Script Date: 2005-9-8 10:28:35 ******/
223
if 224
exists 225
(226
select * 227
from dbo.sysobjects 228
where id = object_id(N'[dbo].[Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1229
)230
drop procedure [dbo].[Search]231
232
GO233
234
SET QUOTED_IDENTIFIER OFF 235
GO236
SET ANSI_NULLS ON 237
GO238
239
240
241
/****** Object: Stored Procedure dbo.Search Script Date: 2005-9-8 10:28:35 ******/242
CREATE proc Search243
@name1 nvarchar(50),244
@name2 nvarchar(50)245
as246
247
--中转站248
create table #tmp249
(250
tmp_id int identity(1,1),251
tmp_name NVARCHAR(50)252
)253
254
--站点队列255
create table #tmp1256
(257
tmp1_id int identity(1,1),258
tmp1_name NVARCHAR(50)259
)260
261
262
--查找结果263
create table #result264
(265
r_id int,266
r_lid nvarchar(50),267
r_name nvarchar(50),268
r_type int269
)270
271
--直达272
insert into #result273
select c.* from t a,t b,t c where 274
a.lid=b.lid and a.[type]=b.[type] and a.id<b.id 275
and a.[name] = @name1 and b.[name] = @name2276
and c.id>=a.id and c.id<=b.id order by c.id277
278
if @@rowcount>0 begin279
select * from #result280
end281
else begin282
--换车283
DECLARE @CurrenName NVARCHAR(50)284
SET @CurrenName = @name1285
change:286
/*287
--车次入栈288
insert into #tmp (tmp_lid)289
select distinct lid from t where [name] = @CurrenName290
DECLARE @CurrenBus NVARCHAR(50)291
SELECT TOP 1 @CurrenBus = tmp_lid FROM #tmp292
*/293
INSERT INTO #tmp1 (tmp1_name)294
SELECT DISTINCT b.[name] FROM t a,t b WHERE a.[name] = @CurrenName AND b.lid = a.lid AND b.[name] <> @CurrenName295
296
INSERT INTO #tmp (tmp_name)297
select d.[tmp1_name] from t a,t b,t c, #tmp1 d where 298
a.lid=b.lid and a.[type]=b.[type] and a.id<b.id 299
and a.[name] = d.[tmp1_name] and b.[name] = @name2300
and c.id>=a.id and c.id<=b.id301
302
IF @@rowcount>0 BEGIN303
select distinct c.* from t a,t b,t c,#tmp d where 304
a.lid=b.lid and a.[type]=b.[type] and a.id<b.id 305
and a.[name] = @name1 and b.[name] = d.tmp_name306
and c.id>=a.id and c.id<=b.id order by c.id307
308
select distinct c.* from t a,t b,t c,#tmp d where 309
a.lid=b.lid and a.[type]=b.[type] and a.id<b.id 310
and a.[name] = d.tmp_name and b.[name] = @name2311
and c.id>=a.id and c.id<=b.id order by c.id312
END313
--SELECT * FROM #tmp314
end315
316
drop table #result317
drop table #tmp1318
drop table #tmp319
GO320
321
SET QUOTED_IDENTIFIER OFF 322
GO323
SET ANSI_NULLS ON 324
GO325
326
exec search '文一路口','总管塘'327
328
329
/*交集操作*/330
select distinct(lid) from t where type=0 and 331
lid in 332
(333
select distinct(lid) from t where type=1334
)335
/*并操作*/336
select distinct(lid) from t where type=0337
union338
select distinct(lid) from t where type=1339
/*差操作*/340
select distinct(lid) from t where type=0 and341
lid not in 342
(343
select distinct(lid) from t where type=1344
)345
346
347
348
349
350
351
浙公网安备 33010602011771号