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

原创 2015年11月19日 20:44:10

实用函数方法


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


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


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


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


指定日期所在周的任意星期几


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 <a href="http://www.jobbole.com/members/wx2197377149">@max</a> AS INT ,@rc AS INT ;

SET @max=10000;

SET @rc=1;

INSERT INTO dbo.Nums VALUES (1);

WHILE @rc * 2 <a href="http://www.jobbole.com/members/wx2197377149">@max</a>

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的值恢复到原是值。


版权声明:

相关文章推荐

你真的会玩SQL吗?内连接、外连接

大多数人一般写多表查询会这样写select * from tbA ,tbB  没有用到JOIN关键字,太Low了,官网标准建议是用JOIN明确表间的关系,下面具体来讲。 连接类型: ...

你真的会玩SQL吗?Top和Apply

本章预先想写一些Top和Apply基本的用法,但好像没什么意义,所以删掉了一些无用的东西,只留下几个示例,以保证系列的完整性。 Top和Apply解决的常见问题,如返回每个雇员的3个最新订单,订单的...
  • wxw_317
  • wxw_317
  • 2015年11月12日 16:20
  • 349

Sql Server 实用函数方法汇总

Sql Server 实用函数方法汇总

PL/SQL实用函数汇总

数值函数 sign(n)函数,n为正数返回1,返回0,负数返回-1: SELECT SIGN (- 20), SIGN (0 ), SIGN( '20' ) ...

【城会玩系列】hdu 5365 Run【计算几何相关】

Run Time Limit: 2000/1000 MS (Java/Others)    Memory Limit: 65536/65536 K (Java/Others) Total Subm...

听说你不会玩直播技术?

你不会,我免费教你

Deeplink做不出效果,那是你不会玩!

根据我们的客户案例,再结合我们的使用经验和相关评估,下面我们提取出了一些Deeplink的进阶玩法。...

【城会玩】hdu 4920 Matrix multiplication【矩阵相乘优化】

Matrix multiplication Time Limit: 4000/2000 MS (Java/Others)    Memory Limit: 131072/131072 K (Java...

歪果仁真会玩,Cortana不喜欢也可移除

微软在Windows 10内置了Cortana这个语音助理,但对于大多数用户来说并不是一个非常实用的功能。我在浏览网页时发现有外国友人已经写了个工具来手动卸载 Cortana,这不由得让人觉得“城会玩...

【城会玩系列】哈理工oj 1423 nyzbfg cevzr【凯撒加密】

哈理工oj 1423 ,hrbust 1423
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:你真的会玩SQL吗?实用函数方法汇总
举报原因:
原因补充:

(最多只允许输入30个字)