实用函数方法
由于有些知识很少被用到，但真需要用时却忘记了又焦头烂额的到处找。
现在将这些‘冷门“却有效的小知识贡献出来，以备不时之需。
|
1
2
3
4
5
|
存储过程中的
''
''
相当于数据库中的‘ 单引号
DECLARE
@str
VARCHAR
(100)
SET
@str=
''
'aaa'
''
SELECT
REPLACE
(@str,
''
''
,
'"'
)
："aaa"
|
|
1
2
|
rtrim :使用 LTRIM 删除字符变量中的前导空格 ; RTRIM 删除字符变量中的尾随空格
rtrim(ltrim(splitdata))
|
|
1
2
3
4
5
6
7
8
|
-- 用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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
用
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
|
|
1
2
3
4
5
6
7
|
Return
执行不成功，中断执行
If Exists(
select
8
from
cb_HsCost
where
ProjectCode = @ProjectCode
and
IsJianAn = 1)
If @ProjectGUID
Is
Null
Begin
Print
'['
+ @ProjectCode +
']：当前指定的项目在当前系统中不存在！'
Return
-1
End
|
|
1
2
3
|
得到包含前月在内的一年时间
SET
@dtBeginDate = getdate()
SET
@dtEndDate = dateadd(
month
,-1,dateadd(
year
,1,@dtBeginDate))
|
高能预警
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
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
年内的第几季
|
快速高效创建数字辅助表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
--创建数字辅助表
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格式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/*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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
--生成副本，按逗号的个数，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) =
','
;
--将含有逗号时候的行输出
|
|
1
2
3
4
5
6
|
在sql server中经常有这样的问题：
一个表采用了自动编号的列之后，由于测试了好多数据，自动编号已累计了上万个。
现在正是要用这个表了，测试数据已经删了，遗留下来的问题 就是在录入新的数据，编号只会继续增加，已使用过的但已删除的编号就不能用了，
谁知道如何解决此问题？
truncate
命令不但会清除所有的数据，还会将IDENTITY的SEED的值恢复到原是值。
|
扫一扫
8275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
