​工作中有许多比较常用的SQL脚本，今天开始分几章分享给大家。

1、行转列的用法PIVOT( , (), , )

(,N,,)

(,N,,)

(,N,,)

(,N,,)

(,N,,)

(,N,,)

(,N,,)

(,N,,)

*

结果：

select ID,NAME,

[1] as 一季度,

[2] as 二季度,

[3] as 三季度,

[4] as 四季度

from

test

pivot

(

sum(number)

for quarter in

([1],[2],[3],[4])

)

as pvt

结果：

2、列转行的用法UNPIOVTcreate table test2

(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)

insert into test2 values(1,苹果,1000,2000,4000,5000)

insert into test2 values(2,梨子,3000,3500,4200,5500)

select * from test2

(提示：可以左右滑动代码)

结果：

--列转行

select id,name,quarter,number

from

test2

unpivot

(

number

for quarter in

([Q1],[Q2],[Q3],[Q4])

)

as unpvt

结果：

3、字符串替换SUBSTRING/REPLACESELECT REPLACE(abcdefg,SUBSTRING(abcdefg,2,4),**)

结果：

SELECT REPLACE(13512345678,SUBSTRING(13512345678,4,11),********)

结果：

SELECT REPLACE(12345678@qq.com,1234567,******)

结果：

4、查询一个表内相同纪录 HAVING

如果一个ID可以区分的话，可以这么写

SELECT * FROM HR.Employees

结果：

select * from HR.Employees

where title in (

select title from HR.Employees

group by title

having count(1)>1)

​结果：

对比一下发现，ID为1,2的被过滤掉了，因为他们只有一条记录

如果几个ID才能区分的话，可以这么写

select * from HR.Employees

where title+titleofcourtesy in

(select title+titleofcourtesy

from HR.Employees

group by title,titleofcourtesy

having count(1)>1)

​结果：

title在和titleofcourtesy进行拼接后符合条件的就只有ID为6,7,8,9的了

5、把多行SQL数据变成一条多列数据，即新增列SELECT

id,

name,

SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) 一季度,

SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) 二季度,

SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) 三季度,

SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) 四季度

FROM test

GROUP BY id,name

结果：

我们将原来的4列增加到了6列。细心的朋友可能发现了这个结果和上面的行转列怎么一模一样？其实上面的行转列是省略写法，这种是比较通用的写法。

6、表复制

语法1：Insert INTO table(field1,field2,...) values(value1,value2,...)

语法2：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）

语法3：SELECT vale1, value2 into Table2 from Table1

（要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。）

语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。

7、利用带关联子查询Update语句更新数据--方法1：

Update Table1

set c = (select c from Table2 where a = Table1.a)

where c is null

--方法2：

update A

set newqiantity=B.qiantity

from A,B

where A.bnum=B.bnum

--方法3：

update

(select A.bnum ,A.newqiantity,B.qiantity from A

left join B on A.bnum=B.bnum) AS C

set C.newqiantity = C.qiantity

where C.bnum =001

8、连接远程服务器--方法1：

select * from openrowset(

SQLOLEDB,

server=192.168.0.1;uid=sa;pwd=password,

SELECT * FROM dbo.test)

--方法2：

select * from openrowset(

SQLOLEDB,

192.168.0.1;

sa;

password,

SELECT * FROM dbo.test)

当然也可以参考以前的示例，建立DBLINK进行远程连接

9、Date 和 Time 样式 CONVERT

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

语法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

可以使用的 style 值：

Style ID

Style 格式

100 或者 0

mon dd yyyy hh:miAM （或者 PM）

101

mm/dd/yy

102

yy.mm.dd

103

dd/mm/yy

104

dd.mm.yy

105

dd-mm-yy

106

dd mon yy

107

Mon dd, yy

108

hh:mm:ss

109 或者 9

mon dd yyyy hh:mi:ss:mmmAM（或者 PM）

110

mm-dd-yy

111

yy/mm/dd

112

yymmdd

113 或者 13

dd mon yyyy hh:mm:ss:mmm(24h)

114

hh:mi:ss:mmm(24h)

120 或者 20

yyyy-mm-dd hh:mi:ss(24h)

121 或者 21

yyyy-mm-dd hh:mi:ss.mmm(24h)

126

yyyy-mm-ddThh:mm:ss.mmm（没有空格）

130

dd mon yyyy hh:mi:ss:mmmAM

131

dd/mm/yy hh:mi:ss:mmmAM

SELECT CONVERT(varchar(100), GETDATE(), 0)

--结果：

12 7 2020 9:33PM

SELECT CONVERT(varchar(100), GETDATE(), 1)

--结果：

12/07/20

SELECT CONVERT(varchar(100), GETDATE(), 2)

--结果：

20.12.07

SELECT CONVERT(varchar(100), GETDATE(), 3)

--结果：

07/12/20

SELECT CONVERT(varchar(100), GETDATE(), 4)

--结果：

07.12.20

SELECT CONVERT(varchar(100), GETDATE(), 5)

--结果：

07-12-20

SELECT CONVERT(varchar(100), GETDATE(), 6)

--结果：

07 12 20

SELECT CONVERT(varchar(100), GETDATE(), 7)

--结果：

12 07, 20

SELECT CONVERT(varchar(100), GETDATE(), 8)

--结果：

21:33:18

SELECT CONVERT(varchar(100), GETDATE(), 9)

--结果：

12 7 2020 9:33:18:780PM

SELECT CONVERT(varchar(100), GETDATE(), 10)

--结果：

12-07-20

SELECT CONVERT(varchar(100), GETDATE(), 11)

--结果：

20/12/07

SELECT CONVERT(varchar(100), GETDATE(), 12)

--结果：

201207

SELECT CONVERT(varchar(100), GETDATE(), 13)

--结果：

07 12 2020 21:33:18:780

SELECT CONVERT(varchar(100), GETDATE(), 14)

--结果：

21:33:18:780

SELECT CONVERT(varchar(100), GETDATE(), 20)

--结果：

2020-12-07 21:33:18

SELECT CONVERT(varchar(100), GETDATE(), 21)

--结果：

2020-12-07 21:33:18.780

SELECT CONVERT(varchar(100), GETDATE(), 22)

--结果：

12/07/20 9:33:18 PM

SELECT CONVERT(varchar(100), GETDATE(), 23)

--结果：

2020-12-07

SELECT CONVERT(varchar(100), GETDATE(), 24)

--结果：

21:33:18

SELECT CONVERT(varchar(100), GETDATE(), 25)

--结果：

2020-12-07 21:33:18.780

SELECT CONVERT(varchar(100), GETDATE(), 100)

--结果：

12 7 2020 9:33PM

SELECT CONVERT(varchar(100), GETDATE(), 101)

--结果：

12/07/2020

SELECT CONVERT(varchar(100), GETDATE(), 102)

--结果：

2020.12.07

SELECT CONVERT(varchar(100), GETDATE(), 103)

--结果：

07/12/2020

SELECT CONVERT(varchar(100), GETDATE(), 104)

--结果：

07.12.2020

SELECT CONVERT(varchar(100), GETDATE(), 105)

--结果：

07-12-2020

SELECT CONVERT(varchar(100), GETDATE(), 106)

--结果：

07 12 2020

SELECT CONVERT(varchar(100), GETDATE(), 107)

--结果：

12 07, 2020

SELECT CONVERT(varchar(100), GETDATE(), 108)

--结果：

21:33:18

SELECT CONVERT(varchar(100), GETDATE(), 109)

--结果：

12 7 2020 9:33:18:780PM

SELECT CONVERT(varchar(100), GETDATE(), 110)

--结果：

12-07-2020

SELECT CONVERT(varchar(100), GETDATE(), 111)

--结果：

2020/12/07

SELECT CONVERT(varchar(100), GETDATE(), 112)

--结果：

20201207

SELECT CONVERT(varchar(100), GETDATE(), 113)

--结果：

07 12 2020 21:33:18:780

SELECT CONVERT(varchar(100), GETDATE(), 114)

--结果：

21:33:18:780

SELECT CONVERT(varchar(100), GETDATE(), 120)

--结果：

2020-12-07 21:33:18

SELECT CONVERT(varchar(100), GETDATE(), 121)

--结果：

2020-12-07 21:33:18.780

以上内容，在工作中比较常用，能记住最好。不能记住就收藏起来，在需要的时候查询即可。