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

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  姓名

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

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

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

SELECT * FROM A
UNION
SELECT * FROM B

SELECT * FROM A
UNION ALL
SELECT * FROM B

SELECT * FROM TestTable ORDER BY NEWID()

SELECT TOP 100 * FROM TestTable ORDER BY NEWID()

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

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

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