博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
你真的会玩SQL吗?实用函数方法汇总
阅读量:6161 次
发布时间:2019-06-21

本文共 5604 字,大约阅读时间需要 18 分钟。

你真的会玩SQL吗?系列目录

      

    

实用函数方法

由于有些知识很少被用到,但真需要用时却忘记了又焦头烂额的到处找。

现在将这些‘冷门“却有效的小知识贡献出来,以备不时之需。

 

存储过程中的 '''' 相当于数据库中的‘ 单引号DECLARE @str VARCHAR(100)SET @str='''aaa'''SELECT REPLACE(@str,'''','"'):"aaa"

 

rtrim :使用 LTRIM 删除字符变量中的前导空格 ; RTRIM 删除字符变量中的尾随空格rtrim(ltrim(splitdata))

 

-- 用select into 把数据放到临时表中,按交费期限排序,并加上idselect identity(int,1,1) as id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight into #tmpfee --select into不需要提前声明临时表#tmpfeefrom z_fee where RentGUID = @strRentGUIDorder by PayLimitdrop table #tmpfee

 

用insert into select 创建临时表 插入自增列  Create Table #Temp_ProjectCodeList        (            RowId int identity(1,1)            ,ProjectCode varchar(100)        )                Insert Into #Temp_ProjectCodeList(ProjectCode)        Select ProjCode From p_Project Where Level = 2                Set @MaxCount = @@RowCount        Set @Count = 1                While @Count <= @MaxCount        Begin            Select @ProjectCode = ProjectCode From #Temp_ProjectCodeList Where RowId = @Count                    Exec usp_cb_BuildHsCost @ProjectCode, @IsExistHsCost, @IsUpdateCsCost                        --调整计数器            Set @Count = @Count + 1        End
Return 执行不成功,中断执行If Exists(select 8 from cb_HsCost where ProjectCode = @ProjectCode and IsJianAn = 1)   If @ProjectGUID Is Null    Begin        Print '[' + @ProjectCode + ']:当前指定的项目在当前系统中不存在!'        Return -1    End
得到包含前月在内的一年时间SET @dtBeginDate = getdate()SET @dtEndDate = dateadd(month,-1,dateadd(year,1,@dtBeginDate))

高能预警

DATEPART ( datepart , date ) datepart函数中一周是周日到周六,而我们通常认为一周是周一到周日 返回表示指定日期的指定日期部分的整数本周第一天 (星期1)select dateadd(wk, datediff(wk,0,getdate()), 0)  本周最后一天(星期天)select dateadd(wk, datediff(wk,0,getdate()), 6) 得到上周一的日期: SELECT DATEADD(day,-DATEPART(weekday,getdate())-5,getdate())得到上周日的日期:SELECT DATEADD(day,-DATEPART(weekday,getdate())+1,getdate()) 得到上个月月末日期:SELECT dateadd(day,-datepart(day,getdate()),getdate())上月第一天SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)  本月第一天select dateadd(dd,-datepart(dd,getdate())+1,getdate())  本月最后一天(当前为2011-03-31时会出错) 选用:select dateadd(dd,-DAY(dateadd(mm,1,'2011-12-20')) ,dateadd(mm,1,'2011-12-20')) select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))  下月第一天  select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))  下月最后一天SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+' 23:59:59'  季度第一天 SELECT   DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)   季度最后一天(直接推算法)  SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()),120)+'1')  季度的最后一天(CASE判断法)  select DATEADD(Month,DATEPART(Quarter,getdate())*3-Month(getdate()),getdate())  本月第一个星期一SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')   今年第一天  SELECT   DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)   今年最后一天  SELECT  dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0))  指定日期所在周的任意一天  SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)--5.指定日期所在周的任意星期几  A.  星期天做为一周的第1天  SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)  B.  星期一做为一周的第1天  SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)  周内的第几日  select datepart(weekday,getdate()) as 周内的第几日  年内的第几周  select datepart(week,getdate()) as 年内的第几周  年内的第几季  select datepart(quarter,getdate()) as 年内的第几季

 快速高效创建数字辅助表

--创建数字辅助表SET NOCOUNT ON   IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums; CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY );  DECLARE @max AS INT ,@rc AS INT ; SET @max=10000; SET @rc=1;  INSERT INTO dbo.Nums VALUES (1); WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums ; SET @rc = @rc * 2; END   INSERT INTO dbo.Nums  SELECT n +  @rc FROM dbo.Nums WHERE n + @rc <= @max;  SELECT COUNT (n) FROM Nums

   练习:将下面表1每行字符串转化为表2格式

/*PlanDetailID  Description1    课程详细安排1,课程详细安排1.1,课程详细安排1.2,课程详细安排1.32    课程详细安排2,课程详细安排2.1,课程详细安排2.23    课程详细安排3,课程详细安排3.1,课程详细安排3.2,课程详细安排3.3,课程详细安排3.44    课程详细安排45    课程详细安排5转化为:PlanDetailID pos Description1    1    课程详细安排11    2    课程详细安排1.11    3    课程详细安排1.21    4    课程详细安排1.32    1    课程详细安排22    2    课程详细安排2.12    3    课程详细安排2.23    1    课程详细安排33    2    课程详细安排3.13    3    课程详细安排3.23    4    课程详细安排3.33    5    课程详细安排3.44    1    课程详细安排45    1    课程详细安排5*/

   参考SQL:

--生成副本,按逗号的个数,n为逗号的位置(默认第一位为逗号)SELECT  PlanDetailID ,        Description ,        nFROM    dbo.T_PlanDetail        INNER JOIN dbo.Nums ON n <= LEN(Description) + 1   --若无AND,则表示按字符个数来生成行数                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--将含有逗号时候的行输出                               --计算每一个字符串的长度SELECT  PlanDetailID ,        SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS element--元素的长度等于下一个逗号的位置减该元素的开始位置FROM    dbo.T_PlanDetail        INNER JOIN dbo.Nums ON n <= LEN(Description) + 1   --若无AND,则表示按字符个数来生成行数                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--将含有逗号时候的行输出                               --计算每个字符串在数组中的位置,按PlanDetailID 分区,按 n 排序SELECT  PlanDetailID ,ROW_NUMBER() OVER(PARTITION BY PlanDetailID ORDER BY n) AS pos,        SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS elementFROM    dbo.T_PlanDetail        INNER JOIN dbo.Nums ON n <= LEN(Description) + 1   --若无AND,则表示按字符个数来生成行数                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--将含有逗号时候的行输出

 

在sql server中经常有这样的问题:一个表采用了自动编号的列之后,由于测试了好多数据,自动编号已累计了上万个。现在正是要用这个表了,测试数据已经删了,遗留下来的问题 就是在录入新的数据,编号只会继续增加,已使用过的但已删除的编号就不能用了, 谁知道如何解决此问题?truncate命令不但会清除所有的数据,还会将IDENTITY的SEED的值恢复到原是值。

   其它干货下载资源已放入微信公众号【一个码农的日常】

转载地址:http://hzefa.baihongyu.com/

你可能感兴趣的文章
算法笔记之高速排序
查看>>
使用 Spring 3 MVC HttpMessageConverter 功能构建 RESTful web 服务
查看>>
一个网络传输框架——zeroMQ 调研笔记
查看>>
HDU ACM 1046 Gridland 找规律
查看>>
[C/C++标准库]_[0基础]_[优先队列priority_queue的使用]
查看>>
一个关于 UIPickerView 的 bug
查看>>
喜欢的名言警句
查看>>
使用 ServiceStack 构建跨平台 Web 服务
查看>>
GraphX中Pregel单源点最短路径(转)
查看>>
zendstudio的安装与配置
查看>>
(转)Java ConcurrentModificationException异常原因和解决方法
查看>>
Cloudera Hadoop 4 实战课程(Hadoop 2.0、集群界面化管理、电商在线查询+日志离线分析)...
查看>>
数据同步
查看>>
Android应用更新自动检测下载
查看>>
android Broadcast广播消息代码实现
查看>>
全息投影技术及其实现(附素材下载)
查看>>
JVM——类的加载过程
查看>>
李洪强-HEAD 和nil和NULL
查看>>
逻辑卷管理LVM (Logical Volume Manager)
查看>>
tomcat使用线程池配置高并发连接
查看>>