Shulou(Shulou.com)05/31 Report--

This article mainly introduces the skills of SQL query, the article is very detailed, has a certain reference value, interested friends must read it!

1. Conversion between rows and rows

Question: suppose there is a student report form (tb) as follows:

Want to become (get the following results):

Code:

WITH tb (name, course, score) AS (SELECT N 'Zhang San', N 'Chinese', 74UNION ALLSELECT N 'Zhang San', N 'Mathematics', 83UNION ALLSELECT N 'Zhang San', N 'Physics', 93UNION ALLSELECT N'Li Si', N 'Chinese', 79UNION ALLSELECT N'Li Si', N 'Mathematics', 86UNION ALLSELECT N'Li Si', N 'Physics', 88) SELECT name, MAX (CASE course WHEN 'Chinese' THEN score ELSE 0 END) MAX (CASE course WHEN 'Mathematics' THEN score ELSE 0 END) Mathematics, MAX (CASE course WHEN 'Physics' THEN score ELSE 0 END) Physics FROM tb GROUP BY name

2. Paging

Plan 1: use NOT IN and SELECT TOP paging statement form

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

Plan 2: use the number of ID greater than and the form of SELECT TOP paging statement

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

Plan 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. Result merging

Merge duplicate rows

SELECT * FROM AUNIONSELECT * FROM B

Do not merge duplicate rows

SELECT * FROM AUNION ALLSELECT * FROM B

4. Random sorting

SELECT * FROM TestTable ORDER BY NEWID ()

It can also be combined with TOP to take the first N random records.

SELECT TOP 100 * FROM TestTable ORDER BY NEWID ()

5. Separate the data on both sides with arbitrary symbols

For example, if we split the data with a comma (,), we would have the following data

It is divided into the following figure:

SELECT Rmaine case WHEN CHARINDEX (',', R) > 1 THEN LEFT (RMagna CHARINDEX (',', R)-1) ELSE NULL END AS R1, CASE WHEN CHARINDEX (',', R) > 1 THEN RIGHT (R, (LEN (R)-CHARINDEX (',', R) ELSE NULL END AS R2FROM t

The code is long, so we split the code to understand:

SELECT CHARINDEX (',')-- the result is 1SELECT CHARINDEX (',', 'NULL')-- the result is 0SELECT CHARINDEX (',',')-- the result is 0SELECT CHARINDEX (',','A,')-- the result is 2SELECT LEN ('A,')-- the result is 3SELECT LEN ('A,')-CHARINDEX (',','A')-- the result is 3-2=1SELECT RIGHT ('A,',') B')-CHARINDEX (',','A _ MagneB'))-- the result is B.

In the last step, we split the'A ~ ~ B 'out of B, and we can get it in a similar way.

6. WAITFOR delay execution

Wait for 1 hour, 2 minutes and 3 seconds before executing the SELECT statement

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

Among them, how long is the delay before DELAY is executed.

For example, the SELECT statement will not be executed until after 11: 08 p.m.

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

Among them, the implementation of TIME does not begin until a specific time.

The above is all the content of this article "SQL query skills". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.