精妙的SQL语句

 

1. 说明：复制表(只复制结构,源表名：a新表名：b)
2. select*intobfromawhere1<>1
3. 说明：拷贝表(拷贝数据,源表名：a目标表名：b)
4. insertintob(a,b,c)selectd,e,ffromb;
5. 说明：显示文章、提交人和最后回复时间
6. selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
7. 说明：外连接查询(表名1：a表名2：b)
8. selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
9. 说明：日程安排提前五分钟提醒
10. select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
11. 说明：两张关联表，删除主表中已经在副表中没有的信息
12. deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)
13. 说明：--
14. SQL:
15. SelectA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE
16. FROMTABLE1,
17. (SelectX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE
18. FROM(SelectNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND
19. FROMTABLE2
20. WhereTO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,
21. (SelectNUM,UPD_DATE,STOCK_ONHAND
22. FROMTABLE2
23. WhereTO_CHAR(UPD_DATE,'YYYY/MM')=
24. TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y,
25. WhereX.NUM=Y.NUM（+）
26. ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)B
27. WhereA.NUM=B.NUM
28. 说明：--
29. select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&strdepartmentname&"'and专业名称='"&strprofessionname&"'orderby性别,生源地,高考总成绩
30. 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
31. Selecta.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')AStelyear,
32. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'01',a.factration))ASJAN,
33. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'02',a.factration))ASFRI,
34. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'03',a.factration))ASMAR,
35. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'04',a.factration))ASAPR,
36. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'05',a.factration))ASMAY,
37. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'06',a.factration))ASJUE,
38. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'07',a.factration))ASJUL,
39. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'08',a.factration))ASAGU,
40. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'09',a.factration))ASSEP,
41. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'10',a.factration))ASOCT,
42. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'11',a.factration))ASNOV,
43. SUM(decode(TO_CHAR(a.telfeedate,'mm'),'12',a.factration))ASDEC
44. FROM(Selecta.userper,a.tel,a.standfee,b.telfeedate,b.factration
45. FROMTELFEESTANDa,TELFEEb
46. Wherea.tel=b.telfax)a
47. GROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')
48. 说明：四表联查问题
49. select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
50. 说明：得到表中最小的未使用的ID号
51. Select(CASEWHENEXISTS(Select*FROMHandlebWhereb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWhereNOTHandleIDIN(Selecta.HandleID-1FROMHandlea)
52. 一个SQL语句的问题:行列转换
53. select*fromv_temp
54. 上面的视图结果如下:
55. user_namerole_name
56. -------------------------
57. 系统管理员管理员
58. feng管理员
59. feng一般用户
60. test一般用户
61. 想把结果变成这样:
62. user_namerole_name
63. ---------------------------
64. 系统管理员管理员
65. feng管理员,一般用户
66. test一般用户
67. ===================
68. createtablea_test(namevarchar(20),role2varchar(20))
69. insertintoa_testvalues('李','管理员')
70. insertintoa_testvalues('张','管理员')
71. insertintoa_testvalues('张','一般用户')
72. insertintoa_testvalues('常','一般用户')
73. createfunctionjoin_str(@contentvarchar(100))
74. returnsvarchar(2000)
75. as
76. begin
77. declare@strvarchar(2000)
78. set@str=''
79. select@str=@str+','+rtrim(role2)froma_testwhere[name]=@content
80. select@str=right(@str,len(@str)-1)
81. return@str
82. end
83. go
84. --调用：
85. select[name],dbo.join_str([name])role2froma_testgroupby[name]
86. --selectdistinctname,dbo.uf_test(name)froma_test
87. 快速比较结构相同的两表
88. 结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
89. ============================
90. 给你一个测试方法，从northwind中的orders表取数据。
91. select*inton1fromorders
92. select*inton2fromorders
93. select*fromn1
94. select*fromn2
95. --添加主键，然后修改n1中若干字段的若干条
96. altertablen1addconstraintpk_n1_idprimarykey(OrderID)
97. altertablen2addconstraintpk_n2_idprimarykey(OrderID)
98. selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1
99. 应该可以，而且将不同的记录的ID显示出来。
100. 下面的适用于双方记录一样的情况，
101. select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1)
102. 至于双方互不存在的记录是比较好处理的
103. --删除n1,n2中若干条记录
104. deletefromn1whereorderIDin('10728','10730')
105. deletefromn2whereorderIDin('11000','11001')
106. --*************************************************************
107. --双方都有该记录却不完全相同
108. select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1)
109. union
110. --n2中存在但在n1中不存的在10728,10730
111. select*fromn1whereorderIDnotin(selectorderIDfromn2)
112. union
113. --n1中存在但在n2中不存的在11000,11001
114. select*fromn2whereorderIDnotin(selectorderIDfromn1)
115. 四种方法取表里n到m条纪录：
116. 1.
117. selecttopm*into临时表(或表变量)fromtablenameorderbycolumnname--将topm笔插入
118. setrowcountn
119. select*from表变量orderbycolumnnamedesc
120. 2.
121. selecttopn*from(selecttopm*fromtablenameorderbycolumnname)aorderbycolumnnamedesc
122. 3.如果tablename里没有其他identity列，那么：
123. selectidentity(int)id0,*into#tempfromtablename
124. 取n到m条的语句为：
125. select*from#tempwhereid0>=nandid0<=m
126. 如果你在执行selectidentity(int)id0,*into#tempfromtablename这条语句的时候报错,那是因为你的DB中间的selectinto/bulkcopy属性没有打开要先执行：
127. execsp_dboption你的DB名字,'selectinto/bulkcopy',true
128. 4.如果表里有identity属性，那么简单：
129. select*fromtablenamewhereidentitycolbetweennandm
130. 如何删除一个表中重复的记录？
131. createtablea_dist(idint,namevarchar(20))
132. insertintoa_distvalues(1,'abc')
133. insertintoa_distvalues(1,'abc')
134. insertintoa_distvalues(1,'abc')
135. insertintoa_distvalues(1,'abc')
136. execup_distinct'a_dist','id'
137. select*froma_dist
138. createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
139. --f_key表示是分组字段﹐即主键字段
140. as
141. begin
142. declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
143. select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
144. exec(@sql)
145. opencur_rows
146. fetchcur_rowsinto@id,@max
147. while@@fetch_status=0
148. begin
149. select@max=@max-1
150. setrowcount@max
151. select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
152. if@type=56
153. select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
154. if@type=167
155. select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
156. exec(@sql)
157. fetchcur_rowsinto@id,@max
158. end
159. closecur_rows
160. deallocatecur_rows
161. setrowcount0
162. end
163. select*fromsystypes
164. select*fromsyscolumnswhereid=object_id('a_dist')
165. 查询数据的最大排序问题（只能用一条语句写）
166. CreateTABLEhard(quchar(11),cochar(11),jenumeric(3,0))
167. insertintohardvalues('A','1',3)
168. insertintohardvalues('A','2',4)
169. insertintohardvalues('A','4',2)
170. insertintohardvalues('A','6',9)
171. insertintohardvalues('B','1',4)
172. insertintohardvalues('B','2',5)
173. insertintohardvalues('B','3',6)
174. insertintohardvalues('C','3',4)
175. insertintohardvalues('C','6',7)
176. insertintohardvalues('C','2',3)
177. 要求查询出来的结果如下：
178. qucoje
179. ---------------------------
180. A69
181. A24
182. B36
183. B25
184. C67
185. C34
186. 就是要按qu分组，每组中取je最大的前2位！！
187. 而且只能用一句sql语句！！！
188. select*fromhardawherejein(selecttop2jefromhardbwherea.qu=b.quorderbyje)
189. 求删除重复记录的sql语句？
190. 怎样把具有相同字段的纪录删除，只留下一条。
191. 例如，表test里有id,name字段
192. 如果有name相同的记录只留下一条，其余的删除。
193. name的内容不定，相同的记录数不定。
194. 有没有这样的sql语句？
195. ==============================
196. A:一个完整的解决方案：
197. 将重复的记录记入temp1表:
198. select[标志字段id],count(*)intotemp1from[表名]
199. groupby[标志字段id]
200. havingcount(*)>1
201. 2、将不重复的记录记入temp1表:
202. inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
203. 3、作一个包含所有不重复记录的表：
204. select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
205. 4、删除重复表:
206. delete[表名]
207. 5、恢复表：
208. insert[表名]select*fromtemp2
209. 6、删除临时表:
210. droptabletemp1
211. droptabletemp2
212. ================================
213. B:
214. createtablea_dist(idint,namevarchar(20))
215. insertintoa_distvalues(1,'abc')
216. insertintoa_distvalues(1,'abc')
217. insertintoa_distvalues(1,'abc')
218. insertintoa_distvalues(1,'abc')
219. execup_distinct'a_dist','id'
220. select*froma_dist
221. createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
222. --f_key表示是分组字段﹐即主键字段
223. as
224. begin
225. declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
226. select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
227. exec(@sql)
228. opencur_rows
229. fetchcur_rowsinto@id,@max
230. while@@fetch_status=0
231. begin
232. select@max=@max-1
233. setrowcount@max
234. select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
235. if@type=56
236. select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
237. if@type=167
238. select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
239. exec(@sql)
240. fetchcur_rowsinto@id,@max
241. end
242. closecur_rows
243. deallocatecur_rows
244. setrowcount0
245. end
246. select*fromsystypes
247. select*fromsyscolumnswhereid=object_id('a_dist')
248. 行列转换--普通
249. 假设有张学生成绩表(CJ)如下
250. NameSubjectResult
251. 张三语文80
252. 张三数学90
253. 张三物理85
254. 李四语文85
255. 李四数学92
256. 李四物理82
257. 想变成
258. 姓名语文数学物理
259. 张三809085
260. 李四859282
261. declare@sqlvarchar(4000)
262. set@sql='selectName'
263. select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']'
264. from(selectdistinctSubjectfromCJ)asa
265. select@sql=@sql+'fromtestgroupbyname'
266. exec(@sql)
267. 行列转换--合并
268. 有表A,
269. idpid
270. 11
271. 12
272. 13
273. 21
274. 22
275. 31
276. 如何化成表B:
277. idpid
278. 11,2,3
279. 21,2
280. 31
281. 创建一个合并的函数
282. createfunctionfmerg(@idint)
283. returnsvarchar(8000)
284. as
285. begin
286. declare@strvarchar(8000)
287. set@str=''
288. select@str=@str+','+cast(pidasvarchar)from表Awhereid=@id
289. set@str=right(@str,len(@str)-1)
290. return(@str)
291. End
292. go
293. --调用自定义函数得到结果
294. selectdistinctid,dbo.fmerg(id)from表A
295. 如何取得一个数据表的所有列名
296. 方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
297. SQL语句如下：
298. declare@objidint,@objnamechar(40)
299. set@objname='tablename'
300. select@objid=idfromsysobjectswhereid=object_id(@objname)
301. select'Column_name'=namefromsyscolumnswhereid=@objidorderbycolid
302. 或
303. Select*FROMINFORMATION_SCHEMA.COLUMNSWhereTABLE_NAME='users'
304. 通过SQL语句来更改用户的密码
305. 修改别人的,需要sysadminrole
306. EXECsp_passwordNULL,'newpassword','User'
307. 如果帐号为SA执行EXECsp_passwordNULL,'newpassword',sa
308. 怎么判断出一个表的哪些字段不允许为空？
309. selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereIS_NULLABLE='NO'andTABLE_NAME=tablename
310. 如何在数据库里找到含有相同字段的表？
311. a.查已知列名的情况
312. Selectb.nameasTableName,a.nameascolumnname
313. FromsyscolumnsaINNERJOINsysobjectsb
314. ONa.id=b.id
315. ANDb.type='U'
316. ANDa.name='你的字段名字'
317. 未知列名查所有在不同表出现过的列名
318. Selecto.nameAstablename,s1.nameAscolumnname
319. Fromsyscolumnss1,sysobjectso
320. Wheres1.id=o.id
321. Ando.type='U'
322. AndExists(
323. Select1Fromsyscolumnss2
324. Wheres1.name=s2.name
325. Ands1.id<>s2.id
326. )
327. 查询第xxx行数据
328. 假设id是主键：
329. select*from(selecttopxxx*fromyourtable)aawherenotexists(select1from(selecttopxxx-1*fromyourtable)bbwhereaa.id=bb.id)
330. 如果使用游标也是可以的
331. fetchabsolute[number]from[cursor_name]
332. 行数为绝对行数
333. SQLServer日期计算
334. a.一个月的第一天
335. SelectDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
336. b.本周的星期一
337. SelectDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
338. c.一年的第一天
339. SelectDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
340. d.季度的第一天
341. SelectDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
342. e.上个月的最后一天
343. Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
344. f.去年的最后一天
345. Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
346. g.本月的最后一天
347. Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
348. h.本月的第一个星期一
349. selectDATEADD(wk,DATEDIFF(wk,0,
350. dateadd(dd,6-datepart(day,getdate()),getdate())
351. ),0)
352. i.本年的最后一天
353. Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。
354. 获取表结构[把'sysobjects'替换成'tablename'即可]
355. SelectCASEIsNull(I.name,'')
356. When''Then''
357. Else'*'
358. EndasIsPK,
359. Object_Name(A.id)ast_name,
360. A.nameasc_name,
361. IsNull(SubString(M.text,1,254),'')aspbc_init,
362. T.nameasF_DataType,
363. CASEIsNull(TYPEPROPERTY(T.name,'Scale'),'')
364. WHEN''ThenCast(A.precasvarchar)
365. ELSECast(A.precasvarchar)+','+Cast(A.scaleasvarchar)
366. ENDasF_Scale,
367. A.isnullableasF_isNullAble
368. FROMSyscolumnsasA
369. JOINSystypesasT
370. ON(A.xType=T.xUserTypeANDA.Id=Object_id('sysobjects'))
371. LEFTJOIN(SysIndexesasI
372. JOINSyscolumnsasA1
373. ON(I.id=A1.idandA1.id=object_id('sysobjects')and(I.status&0x800)=0x800ANDA1.colid<=I.keycnt))
374. ON(A.id=I.idANDA.name=index_col('sysobjects',I.indid,A1.colid))
375. LEFTJOINSysCommentsasM
376. ON(M.id=A.cdefaultandObjectProperty(A.cdefault,'IsConstraint')=1)
377. orDERBYA.ColidASC
378. 提取数据库内所有表的字段详细说明的SQL语句
379. Select
380. (casewhena.colorder=1thend.nameelse''end)N'表名',
381. a.colorderN'字段序号',
382. a.nameN'字段名',
383. (casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''
384. end)N'标识',
385. (casewhen(Selectcount(*)
386. FROMsysobjects
387. Where(namein
388. (Selectname
389. FROMsysindexes
390. Where(id=a.id)AND(indidin
391. (Selectindid
392. FROMsysindexkeys
393. Where(id=a.id)AND(colidin
394. (Selectcolid
395. FROMsyscolumns
396. Where(id=a.id)AND(name=a.name)))))))AND
397. (xtype='PK'))>0then'√'else''end)N'主键',
398. b.nameN'类型',
399. a.lengthN'占用字节数',
400. COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度',
401. isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数',
402. (casewhena.isnullable=1then'√'else''end)N'允许空',
403. isnull(e.text,'')N'默认值',
404. isnull(g.[value],'')ASN'字段说明'
405. FROMsyscolumnsa
406. leftjoinsystypesb
407. ona.xtype=b.xusertype
408. innerjoinsysobjectsd
409. ona.id=d.idandd.xtype='U'andd.name<>'dtproperties'
410. leftjoinsyscommentse
411. ona.cdefault=e.id
412. leftjoinsyspropertiesg
413. ona.id=g.idANDa.colid=g.smallid
414. orderbyobject_name(a.id),a.colorder
415. 快速获取表test的记录总数[对大容量表非常有效]
416. 快速获取表test的记录总数:
417. selectrowsfromsysindexeswhereid=object_id('test')andindidin(0,1)
418. update2setKHXH=(ID+1)\22行递增编号
419. update[23]setid1='No.'+right('00000000'+id,6)whereidnotlike'No%'//递增
420. update[23]setid1='No.'+right('00000000'+replace(id1,'No.',''),6)//补位递增
421. deletefrom[1]where(id%2)=1
422. 奇数
423. 替换表名字段
424. update[1]setdomurl=replace(domurl,'Upload/Imgswf/','Upload/Photo/')wheredomurllike'%Upload/Imgswf/%'
425. 截位
426. SelectLEFT(表名,5)