平时常用sql

 

总结一下平时用到最多的sql语句

 

1.特殊日期

 1 --今天凌晨
 2 select dateadd(dd,datediff(dd,0,getdate()),0)
 3 --明天凌晨
 4 select dateadd(dd,datediff(dd,0,getdate())+1,0)
 5 --当周周一(每周从周日开始)
 6 select dateadd(wk,datediff(wk,0,getdate()),0)
 7 --当月的第一天
 8 select dateadd(mm,datediff(mm,0,getdate()),0)
 9 --当月的最后一天
10 select dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate())+1,0))
11 --今年的第一天
12 select dateadd(yy,datediff(yy,0,getdate()),0)
13 --今年的最后一天
14 select dateadd(dd,-1,dateadd(yy,datediff(yy,0,getdate())+1,0))

 

2.字符串处理

 1 --去除空格
 2 select ltrim(col1),rtrim(col2),ltrim(rtrim(col3)) from tablename
 3 
 4 select 
 5 substring(col1,3,2) --从第3个字符开始截取2个,得到截取的2个字符
 6 ,stuff(col2,3,2,'')--从第3个字符开始删除2个,得到剩下字符
 7 ,stuff(col3,3,2,'xxx')--从第3个字符开始,将第3、4两个字符替换成xxx
 8 ,replace(col4,'old','new')--将col4中的old全部替换成new
 9 from tablename
10 
11 
12 --将列col1用,拼接起来
13 select stuff((select ','+col1 from tablename where 过滤条件 for xml path('')),1,1,'')
14 
15 
16 --多位流水号,用0或空格补充 例如a000001,a000002……，前缀可为固定字符，或者可变的年、月、日等
17 declare @flowno as varchar(10)
18 declare @nextno as int
19 
20 select @flowno = max(flowno) from tablename where 过滤条件 
21 if(@flowno is null)
22 　　set @flowno = 'a000001'
23 else 
24 　　begin
25 　　　　set @nextno = right(@flowno,6)+1
26 　　　　set @flowno = left(@flowno,1)+replicate('0',6-len(@nextno))+convert(varchar(6),@nextno)
27 　　end 
28 select @flowno;--要获取的流水号
29 
30  
31 
32 --年 2001对应1,2009对应9,2010对应a,2035对应z,2035之后的可自行调整
33 select substring('123456789abcdefghijklmnopqrstuvwxyz',(year(getdate())-2000)%36,1)
34 
35 --月 10月,11月,12月分别对应a,b,c
36 select substring('123456789abc',month(getdate()),1)
37 
38 --月 英文简写
39 select substring('janfebmaraprmayjunjulaugsepoctnovdec',(month(getdate())-1)*3+1,3)
40 
41 --日 1号对应1,9号对应9,10号对应a,以此类推
42 select substring('123456789abcdefghijklmnopqrstuv',day(getdate()),1)

 

 

3.辅助

 1 --去除重复数据,数据表中有很多重复数据，如果其中col1,col2,col3,col4,col5可以表示出每一组重复数据
 2 ;with cte as
 3 　　(select col1,col2,col3,col4,col5
 4 　　,row_number()over(partition by col1,col2,col3,col4,col5 order by col1) as rn 
 5 　　from tablename)
 6 delete from cte where rn > 1
 7 
 8 
 9 --快速打开存储过程、视图等
10 sp_helptext 存储过程名称 --名称前不要加dbo.等所有者
11 sp_helptext 视图名称 --名称前不要加dbo.等所有者
12 
13 
14 --用到了某些字符串的视图,存储过程
15 select a.name,b.[text] 
16 from sysobjects a 
17 inner join syscomments b on a.id = b.id
18 where b.[text] like '%你要查的字符串%'
19 and a.xtype='v'--'p'
20 
21 
22 --获取表tablename中所有的列名,并用组合。写insert的时候简直不要太爽
23 select stuff((select ','+name 
24 　　from syscolumns 
25 　　where id = object_id('tablename') 
26 　　order by colorder   --colorder:按照列的添加顺序    name:按照列名顺序
27 　　for xml path('')),1,1,'')