Quietly share 6 SQL query tips

Recommended (free): SQL tutorial

1. Row and column conversion

Question: Suppose there is a student score table (tb) as follows:

Want to become (obtain the following results):

Code:

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

2. Paging

Option 1: Use the NOT IN and SELECT TOP paging statement forms

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

Option 2: Use the ID greater than and SELECT TOP paging statement form

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

Option 3: Use the feature ROW_NUMBER in SQL Server for paging

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

3. Merge results

Merge duplicates Rows

 SELECT * FROM A
UNION
SELECT * FROM B

Do not merge duplicate rows

SELECT * FROM A
UNION ALL
SELECT * FROM B

4. Random sorting

SELECT * FROM TestTable ORDER BY NEWID()

can also be combined with TOP Random first N records

SELECT TOP 100 * FROM TestTable ORDER BY NEWID()

5. Separate the data on both sides with any symbol

For example, if we use commas (,) to separate the data, it will be as follows The data

is divided into as shown below:

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

The code is long, we split the code Understanding:

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

In the last step, we split 'A, B' into B. Similarly, we can also obtain A using a similar method.

6. WAITFOR delayed execution

For example, wait for 1 hour, 2 minutes and 3 seconds before executing the SELECT statement

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

DELAY is the delay before execution starts.

Example: Wait until 11:08 pm before executing the SELECT statement

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

TIME is to wait until a specific time to start execution

我是岳哥，最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行数据前线
——End——

后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读
27岁发明SQL以后，上帝把他带走了

微信8.0不好玩？那可能是你打开方式不正确！

一个员工的离职成本有多恐怖！
SQL养成这些好习惯是一笔财富
MySQL基本知识点梳理和查询优化

More related free learning recommendations: SQLserver

The above is the detailed content of Quietly share 6 SQL query tips. For more information, please follow other related articles on the PHP Chinese website!