0%

sqlserver常用sql整理

xml 使用

xml 有不少用处,用好了可以很方便的处理数据,下面提供两个运用范例。

将以某字符分隔的字符串转成表

有一批身份证,但是字符串,由英文逗号分隔,像这样 110101197004162031,110101197105101553,110101197108281510,110101197210024537,现在要实现用这些身份证去某个表里查出身份证对应的信息。

可以在条件中使用 in 查询,但是数据量较大时效率比较低。

可以通过 xml 生成一张临时表,如下 sql 范例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @Ids NVARCHAR(MAX)= '110101197004162031,110101197105101553,110101197108281510,110101197210024537'
DECLARE @xml_ids XML;
SELECT @xml_ids = CONVERT(XML, '<root><bid>' + REPLACE(@Ids, ',', '</bid><bid>') + '</bid></root>')

IF OBJECT_ID(N'tempdb..#tempId', N'U') IS NOT NULL
DROP TABLE #tempId

CREATE TABLE #tempId
(
IdentityNo VARCHAR(18) PRIMARY KEY ,
Id INT IDENTITY
)

INSERT INTO #tempId ( IdentityNo )
SELECT Bid.value('.', 'varchar(18)')
FROM @xml_ids.nodes('/root/bid') x (bid)

执行结果:

IdentityNo Id
110101197004162031 1
110101197105101553 2
110101197108281510 3
110101197210024537 4

将表字段多行转成一个字符串

和上面的【将以某字符分隔的字符串转成表】相反,将表字段多行转成以某字符分隔的字符串。

语法:

1
2
3
4
5
6
7
8
9
10
11
SELECT STUFF(
(
SELECT ',' + t.字段
FROM 表 t
WHERE 条件
FOR XML PATH('')
),
1,
1,
''
);

如表:

1
2
3
SELECT TOP 10 t.VbsKey
FROM Cooperation.WMXWDictKeyMapping t
WHERE t.VbsCode = 'BankAccountId';

转换:

1
2
3
4
5
6
7
8
9
10
11
SELECT STUFF(
(
SELECT TOP 10 ',' + t.VbsKey
FROM Cooperation.WMXWDictKeyMapping t
WHERE t.VbsCode = 'BankAccountId'
FOR XML PATH('')
),
1,
1,
''
);

取到的就是 103,104,108,14,109,110,111,113,125,126

日期常用操作

日期格式化

常用的日期格式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT CONVERT(CHAR(20), GETDATE(), 20)  --2019-10-12 10:21:20 
SELECT CONVERT(char(24), GETDATE(), 21) --2019-10-12 10:21:20.640
select convert(char(10),GetDate(),101) --mm/dd/yyyy
select convert(char(10),GetDate(),102) --yyyy.mm.dd
select convert(char(10),GetDate(),103) --dd/mm/yyyy
select convert(char(10),GetDate(),108) --hh:mi:ss(时间)
select convert(char(10),GetDate(),111) --yyyy/mm/dd
select convert(char(10),GetDate(),112) --yyyymmdd
select convert(char(12),GetDate(),114) --10:18:31:303
select convert(char(10),GetDate(),120) --yyyy-mm-dd


SELECT CONVERT(varchar(20), GETDATE(), 20) --2019-10-12 10:24:37
SELECT CONVERT(varchar(10), GETDATE(), 23) --2019-10-12
SELECT CONVERT(varchar(25), GETDATE(), 25) --2019-10-12 10:25:48.613
SELECT CONVERT(varchar(10), GETDATE(), 102) --2019.10.12
SELECT CONVERT(varchar(10), GETDATE(), 108) --10:26:19
SELECT CONVERT(varchar(10), GETDATE(), 111) --2019/10/12
SELECT CONVERT(varchar(8), GETDATE(), 112) --20191012
SELECT CONVERT(varchar(12), GETDATE(), 114) --10:27:04:917
SELECT CONVERT(varchar(20), GETDATE(), 120) --2019-10-12 10:27:24
SELECT CONVERT(varchar(25), GETDATE(), 121) --2019-10-12 10:27:42.680

获取日期范围

在报表统计时,可能会查看任何日期范围的数据,下面的 sql 可通过两个参数的设置来获取任何日期范围:

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
DECLARE @beforeDay INT = 7;
DECLARE @beforeDaysBaseToday INT = -1;
DECLARE @baseDate DATE;
DECLARE @startDate DATETIME;
SET @baseDate = DATEADD(DAY, -@beforeDaysBaseToday, GETDATE());
SELECT @startDate = DATEADD(DAY, -@beforeDay, @baseDate);
DECLARE @endDate DATETIME;
IF @beforeDay <> 0
BEGIN
SET @endDate = DATEADD(SECOND,@beforeDay-1,DATEADD(SECOND, -@beforeDay, CONVERT(DATETIME, @baseDate)));
END;
ELSE
BEGIN
IF @beforeDaysBaseToday = 0
BEGIN
SET @endDate = DATEADD(DAY, -@beforeDay, GETDATE());
END
ELSE
BEGIN
SET @endDate =DATEADD(SECOND,-1,DATEADD(DAY,1,CONVERT(DATETIME, @baseDate)));
END
END;

SELECT GETDATE() AS 脚本执行时间,
@baseDate AS 基于的日期,
@startDate AS 统计开始时间,
@endDate AS 统计结束时间;

参数说明:

  • beforeDay:多少天前
  • beforeDaysBaseToday:基于今天的多少天前

startDate 和 endDate 就是最终要使用的开始日期和结束日期。

范例说明:

1.如果 beforeDaysBaseToday=0,beforeDays=1 就是昨天1天的数据

脚本执行时间 基于的日期 统计开始时间 统计结束时间
2020-05-19 15:30:51.423 2020-05-19 2020-05-18 00:00:00.000 2020-05-18 23:59:59.000

2.如果 beforeDaysBaseToday=1 beforeDays=1 就是前天1天的数据(基于昨天的一天前)

脚本执行时间 基于的日期 统计开始时间 统计结束时间
2020-05-19 15:31:40.813 2020-05-18 2020-05-17 00:00:00.000 2020-05-17 23:59:59.000

3.如果 beforeDaysBaseToday=0,beforeDays=2 就是前2天的数据

脚本执行时间 基于的日期 统计开始时间 统计结束时间
2020-05-19 15:32:27.393 2020-05-19 2020-05-17 00:00:00.000 2020-05-18 23:59:59.000

4.如果 beforeDaysBaseToday=0,beforeDays=0 就是当天凌晨到当前时间的数据,以此类推

脚本执行时间 基于的日期 统计开始时间 统计结束时间
2020-05-19 15:32:52.020 2020-05-19 2020-05-19 00:00:00.000 2020-05-19 15:32:52.020

获取表结构信息

获取表字段信息:

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
SELECT col.name AS 列名,
typ.name AS 数据类型,
col.max_length AS 占用字节数,
col.precision AS 数字长度,
col.scale AS 小数位数,
col.is_nullable AS 是否允许非空,
col.is_identity AS 是否自增,
CASE
WHEN EXISTS
(
SELECT 1
FROM sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id)
WHERE idx.object_id = col.object_id
AND idxCol.index_column_id = col.column_id
AND idx.is_primary_key = 1
) THEN
1
ELSE
0
END AS 是否是主键,
ISNULL(g.[value], '-') AS 说明
FROM sys.columns col
LEFT JOIN sys.types typ
ON (col.system_type_id = typ.system_type_id)
LEFT JOIN sys.extended_properties g
ON (
col.object_id = g.major_id
AND g.minor_id = col.column_id
)
WHERE col.object_id =
(
SELECT object_id FROM sys.tables WHERE name = 'FoticFileUpload'
);

执行sql字符串

1
DECLARE @Sql NVARCHAR(MAX)='sqlXX';EXEC sp_executesql  @Sql;

注:sp_executesql,过程需要类型为 ‘ntext/nchar/nvarchar’ 的参数 ‘@statement’。

如果 sql 中有单引号,则需要使用转义符,sqlserver 中使用英文单引号可实现转义。

范例:

1
DECLARE @Sql NVARCHAR(MAX)='SELECT * FROM dbo.GoodMan WHERE UserName=''kungge'' ';EXEC sp_executesql @Sql;

改变表结构

表添加字段

语法:

1
2
3
4
5
USE [数据库名]
GO

ALTER TABLE 表名 ADD 字段名 类型 默认值 是否为空;
EXEC sp_addextendedproperty N'MS_Description', N'字段说明', N'SCHEMA', N'dbo',N'TABLE', N'表名', N'COLUMN', N'字段名';

实例:

1
2
3
4
ALTER TABLE  customer.Blacklist ADD ImportOperator INT DEFAULT((0));
ALTER TABLE customer.Blacklist ADD ImportTime DATETIME DEFAULT((GETDATE()));
EXEC sp_addextendedproperty N'MS_Description', N'导入操作者', N'SCHEMA', N'customer',N'TABLE', N'Blacklist', N'COLUMN', N'ImportOperator';
EXEC sp_addextendedproperty N'MS_Description', N'导入时间', N'SCHEMA', N'customer',N'TABLE', N'Blacklist', N'COLUMN', N'ImportTime';

修改字段属性

语法:

1
EXEC sp_updateextendedproperty N'MS_Description', N'字段说明', N'SCHEMA', N'架构名',N'TABLE', N'表名', N'COLUMN', N'列名';

范例:

1
EXEC sp_updateextendedproperty N'MS_Description', N'发送标志:外贸(0 未发,1 已发),晋商(0 未发,1 已发,>1 已发&文件Id)', N'SCHEMA', N'lending',N'TABLE', N'Lending', N'COLUMN', N'IsSend';

系统信息

查看数据库版本

SELECT @@version;

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

您的支持是我最大的动力!