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

This article introduces the relevant knowledge of "what SQL query tips". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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', 74 UNION ALL SELECT N 'Zhang San', N 'Mathematics', 83 UNION ALL SELECT N 'Zhang San', N 'Physics', 93 UNION ALL SELECT N'Li Si', N 'Chinese', 79 UNION ALL SELECT N'Li Si', N 'Mathematics', 86 UNION ALL SELECT N'Li Si', N 'Physics', 88) SELECT name MAX (CASE course WHEN 'Chinese' THEN score ELSE 0 END) Chinese, MAX (CASE course WHEN 'Mathematics' THEN score ELSE 0 END) Mathematics, MAX (CASE course WHEN 'Physics' THEN score ELSE 0 END) physical FROM tb GROUP BY name

2. Paging

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

SELECT TOP 10 * FROM TestTable WHERE 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 TestTable WHERE 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 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 ()

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 R, CASE WHEN CHARINDEX (',', R) > 1 THEN LEFT (Rdachardex (',', 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, so we split the code to understand:

SELECT CHARINDEX (',')-- the result is 1 SELECT CHARINDEX (',', 'NULL')-- the result is 0 SELECT CHARINDEX (',',')-- the result is 0 SELECT CHARINDEX (',')-- the result is 2 SELECT LEN ('Ameme B')-- the result is 3 SELECT LEN (' AMagneB')-CHARINDEX (',', 'AMagneB')-- the result is 3-2 minutes 1 SELECT RIGHT (' AMagee B') (LEN ('A ~ ~ B')-CHARINDEX (',','A ~)-- 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 FROM Employee 02lo 03' SELECT *

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 FROM Employee 08 SELECT * FROM Employee

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

This is the end of the content of "what SQL query Tips". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.