几个SQL查询小技巧，你学会几个？

​1、行列转换

问题：假设有张学生成绩表(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 GROUPBY  姓名

2、分页

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

SELECT TOP 10*FROM TestTable
WHERE ID NOTIN
(SELECT TOP 20 ID FROM TestTable ORDERBY ID)
ORDERBY ID

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

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

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

SELECT*FROM(
SELECT ROW_NUMBER() OVER(ORDERBY ID DESC)AS ROWID,*
FROM TestTable
)AS mytable where ROWID between21and40

3、结果合并

合并重复行。

SELECT*FROM A
UNION
SELECT*FROM B

不合并重复行。

SELECT*FROM A
UNION ALL
SELECT*FROM B

4、随机排序

SELECT*FROM TestTable ORDERBY NEWID()

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

SELECT TOP 100*FROM TestTable ORDERBY NEWID()

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

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

分割成如下图所示：

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我们也可以用类似的方法获取到。

6、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是等到具体某个时刻才开始执行​。