你真的会玩SQL吗？实用函数方法汇总

时间：2023-12-16 09:17:38

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

你真的会玩SQL吗？之逻辑查询处理阶段

你真的会玩SQL吗？和平大使 内连接、外连接

你真的会玩SQL吗？三范式、数据完整性

你真的会玩SQL吗？查询指定节点及其所有父节点的方法

你真的会玩SQL吗？让人晕头转向的三值逻辑

你真的会玩SQL吗？EXISTS和IN之间的区别

你真的会玩SQL吗？无处不在的子查询

你真的会玩SQL吗？Case也疯狂

你真的会玩SQL吗？表表达式，排名函数

你真的会玩SQL吗？简单的 数据修改

你真的会玩SQL吗？你所不知道的 数据聚合

你真的会玩SQL吗？透视转换的艺术

你真的会玩SQL吗？冷落的Top和Apply

你真的会玩SQL吗？实用函数方法汇总

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

　  你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（下）

实用函数方法

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

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

存储过程中的 '''' 相当于数据库中的‘ 单引号

DECLARE @str VARCHAR(100)

SET @str='''aaa'''

SELECT REPLACE(@str,'''','"')

："aaa"
rtrim :使用 LTRIM 删除字符变量中的前导空格 ; RTRIM 删除字符变量中的尾随空格

rtrim(ltrim(splitdata))
-- 用select into 把数据放到临时表中，按交费期限排序，并加上id

select identity(int,1,1) as id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight

into #tmpfee --select into不需要提前声明临时表#tmpfee

from z_fee

where RentGUID = @strRentGUID

order by PayLimit

drop 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)+'')

季度的最后一天（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  Description

1    课程详细安排1,课程详细安排1.1,课程详细安排1.2,课程详细安排1.3

2    课程详细安排2,课程详细安排2.1,课程详细安排2.2

3    课程详细安排3,课程详细安排3.1,课程详细安排3.2,课程详细安排3.3,课程详细安排3.4

4    课程详细安排4

5    课程详细安排5

转化为：

PlanDetailID pos Description

1    1    课程详细安排1

1    2    课程详细安排1.1

1    3    课程详细安排1.2

1    4    课程详细安排1.3

2    1    课程详细安排2

2    2    课程详细安排2.1

2    3    课程详细安排2.2

3    1    课程详细安排3

3    2    课程详细安排3.1

3    3    课程详细安排3.2

3    4    课程详细安排3.3

3    5    课程详细安排3.4

4    1    课程详细安排4

5    1    课程详细安排5

*/

　　参考SQL：

--生成副本，按逗号的个数，n为逗号的位置（默认第一位为逗号）

SELECT  PlanDetailID ,

        Description ,

        n

FROM    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 element

FROM    dbo.T_PlanDetail

        INNER JOIN dbo.Nums ON n <= LEN(Description) + 1   --若无AND，则表示按字符个数来生成行数

                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--将含有逗号时候的行输出 
在sql server中经常有这样的问题：

一个表采用了自动编号的列之后，由于测试了好多数据，自动编号已累计了上万个。

现在正是要用这个表了，测试数据已经删了，遗留下来的问题 就是在录入新的数据，编号只会继续增加，已使用过的但已删除的编号就不能用了，

 谁知道如何解决此问题？

truncate命令不但会清除所有的数据，还会将IDENTITY的SEED的值恢复到原是值。

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

相关文章