SQL常用脚本整理，你保存了吗？

​工作中有许多比较常用的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、列转行的用法UNPIOVT

createtable test2
(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insertinto test2 values(1,'苹果',1000,2000,4000,5000)
insertinto 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/REPLACE

SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')

结果：

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

结果：

SELECT REPLACE('[email protected]','1234567','******')

结果：

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

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

SELECT*FROM HR.Employees

结果：

select*from HR.Employees
where title in(
select title from HR.Employees
groupby title
havingcount(1)>1)

​结果：

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

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

select*from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
groupby title,titleofcourtesy
havingcount(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
GROUPBY 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 isnull

--方法2：
update  A
set  newqiantity=B.qiantity
from  A,B
where  A.bnum=B.bnum

--方法3：
update
(select A.bnum,A.newqiantity,B.qiantityfrom 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 值：

SELECT CONVERT(varchar(100), GETDATE(),0)
--结果：
12720209: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)
--结果：
071220
SELECT CONVERT(varchar(100), GETDATE(),7)
--结果：
1207,20
SELECT CONVERT(varchar(100), GETDATE(),8)
--结果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(),9)
--结果：
12720209: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)
--结果：
0712202021:33:18:780
SELECT CONVERT(varchar(100), GETDATE(),14)
--结果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(),20)
--结果：
2020-12-0721:33:18
SELECT CONVERT(varchar(100), GETDATE(),21)
--结果：
2020-12-0721:33:18.780
SELECT CONVERT(varchar(100), GETDATE(),22)
--结果：
12/07/209: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-0721:33:18.780
SELECT CONVERT(varchar(100), GETDATE(),100)
--结果：
12720209: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)
--结果：
07122020
SELECT CONVERT(varchar(100), GETDATE(),107)
--结果：
1207,2020
SELECT CONVERT(varchar(100), GETDATE(),108)
--结果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(),109)
--结果：
12720209: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)
--结果：
0712202021:33:18:780
SELECT CONVERT(varchar(100), GETDATE(),114)
--结果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(),120)
--结果：
2020-12-0721:33:18
SELECT CONVERT(varchar(100), GETDATE(),121)
--结果：
2020-12-0721:33:18.780

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