SQL常用脚本

SQL

1、行转列的用法PIVOT

CREATE table test
(id int,name nvarchar(20),quarter int,number int)
insert into test values(1,N'苹果',1,1000)
insert into test values(1,N'苹果',2,2000)
insert into test values(1,N'苹果',3,4000)
insert into test values(1,N'苹果',4,5000)
insert into test values(2,N'梨子',1,3000)
insert into test values(2,N'梨子',2,3500)
insert into test values(2,N'梨子',3,4200)
insert into test values(2,N'梨子',4,5500)
select * from test

结果：

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

create 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/REPLACE

SELECT 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 值：

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

10、行列转换的普通实现

问题：假设有张学生成绩表(tb)如下:

想变成(得到如下结果)：

代码：

WITH tb(姓名,课程,分数) AS
(
SELECT N'张三',N'语文',74
UNION ALL
SELECT N'张三',N'数学',83
UNION ALL
SELECT N'张三',N'物理',93
UNION ALL
SELECT N'李四',N'语文',79
UNION ALL
SELECT N'李四',N'数学',86
UNION ALL
SELECT N'李四',N'物理',88
)
SELECT 姓名 ,
MAX(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END) 语文,
MAX(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END) 数学,
MAX(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END) 物理
FROM tb GROUP BY  姓名

11、分页

方案一：利用NOT IN和SELECT TOP分页语句形式

SELECT TOP 10 * FROM TestTable
WHERE ID NOT IN
(SELECT TOP 20 ID FROM TestTable ORDER BY ID)
ORDER BY ID

方案二：利用ID大于多少和SELECT TOP分页语句形式

SELECT TOP 10 * FROM TestTable
WHERE ID > (
  SELECT MAX(id) FROM
  (SELECT TOP 20 id FROM
   TestTable ORDER BY id) AS T)
ORDER BY ID

方案三：利用SQL Server中的特性ROW_NUMBER进行分页

SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROWID,*
  FROM TestTable
) AS mytable where ROWID between 21 and 40

12、结果合并

合并重复行

SELECT * FROM A
UNION
SELECT * FROM B

不合并重复行

SELECT * FROM A
UNION ALL
SELECT * FROM B

13、随机排序

SELECT * FROM TestTable ORDER BY NEWID()

还可以结合TOP取随机的前N条记录

SELECT TOP 100 * FROM TestTable ORDER BY NEWID()

14、以任意符号分隔取两边数据

例如以逗号(,)来分割数据，将如下数据

分割成如下图所示：

SELECT R,
CASE WHEN  CHARINDEX(',',R)>1 THEN  LEFT(R,CHARINDEX(',',R)-1) ELSE NULL END AS R1 ,
CASE WHEN CHARINDEX(',',R)>1 THEN RIGHT(R,(LEN(R) - CHARINDEX(',',R))) ELSE NULL END AS R2
FROM  t

代码较长，对代码进行拆分来理解：

SELECT  CHARINDEX(',',',') --结果是1
SELECT  CHARINDEX(',','NULL') --结果是0
SELECT  CHARINDEX(',','') --结果是0
SELECT  CHARINDEX(',','A,B') --结果是2
SELECT  LEN('A,B') --结果是3
SELECT  LEN('A,B') - CHARINDEX(',','A,B') --结果是3-2=1
SELECT  RIGHT('A,B',( LEN('A,B') - CHARINDEX(',','A,B'))) --结果是 B

最后一步将’A,B’拆分出来了B，同理A也可以用类似的方法获取到。

15、WAITFOR延时执行

例 等待1 小时2 分零3 秒后才执行SELECT 语句

WAITFOR DELAY '01:02:03'
SELECT * FROM Employee

其中 DELAY是在延时多长时间后才开始执行。
例 等到晚上11 点零8 分后才执行SELECT 语句

WAITFOR TIME '23:08:00'
SELECT * FROM Employee

其中TIME是等到具体某个时刻才开始执行