Click on “SQL database development”, \

Set it as “top or star mark” to deliver dry goods in the first time

1, row conversion \

Question: Suppose there is a student grade sheet (TB) as follows :\

Want to become (get the following result) : \

Code: \

WITH TB AS (SELECT N'Joe',N'Chinese'.74
UNION ALL
SELECT N'Joe',N'mathematics'.83
UNION ALL
SELECT N'Joe',N'physical'.93
UNION ALL
SELECT N'bill',N'Chinese'.79
UNION ALL
SELECT N'bill',N'mathematics'.86
UNION ALL
SELECT N'bill',N'physical'.88SELECT name, MAX(CASE course WHEN'Chinese'THEN score ELSE0END) Chinese, MAX(CASE course WHEN'mathematics'THEN score ELSE0END) Math, MAX(CASE course WHEN'physical'THEN score ELSE0END) Physical FROM TB GROUP BY NameCopy the code

2, paging \

Option 1: use the NOT IN and SELECT TOP page statement form \

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

Option 2: use ID greater than what and SELECT TOP page 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
Copy the code

Scheme 3: Use the SQL Server feature ROW_NUMBER to perform paging

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

3, result merge \

Merge duplicate lines \

 SELECT * FROM A
UNION
SELECT * FROM B
Copy the code

Do not merge duplicate lines \

SELECT * FROM A
UNION ALL
SELECT * FROM B
Copy the code

4, random sort \

SELECT * FROM TestTable ORDER BY NEWID()
Copy the code

You can also combine TOP to pick the first N records randomly

SELECT TOP 100 * FROM TestTable ORDER BY NEWID()
Copy the code

5, with any symbol to separate the two sides of the data \

For example, if we split the data with a comma (,), we would divide the data as follows

As shown in the figure 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
Copy the code

The code is longer, we break the code to understand: \

SELECT  CHARINDEX(', '.', ') -- turns out1
SELECT  CHARINDEX(', '.'NULL') -- turns out0
SELECT  CHARINDEX(', '.' ') -- turns out0
SELECT  CHARINDEX(', '.'A,B') -- turns out2
SELECT  LEN('A,B') -- turns out3
SELECT  LEN('A,B') - CHARINDEX(', '.'A,B') -- turns out32 -=1
SELECT  RIGHT('A,B',( LEN('A,B') - CHARINDEX(', '.'A,B'() BCopy the code

In the last step we split ‘A ‘, ‘B’ out of’ B’, and we can get ‘A ‘in A similar way. \

6, WAITFOR delay \

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

WAITFOR DELAY '01:02:03'
SELECT * FROM Employee
Copy the code

Where DELAY is how long the execution starts after the DELAY. \

Example Wait until 11:08 PM to execute the SELECT statement \

WAITFOR TIME '23:08:00'
SELECT * FROM Employee
Copy the code

Where TIME is executed at a specific TIME

I am my brother Yue, and finally I would like to share with you the PDF electronic version of MY SQL two-piece set: "SQL Basics 2nd Edition" and "SQL Advanced 2nd Edition". There are various grammar explanations, a large number of examples and annotations, and so on, very easy to understand, convenient for everyone to follow together to practice. There is a need for readers can download learning, in the following public number "data front" (not this number) back keyword: SQL, on the line data front -- End -- back keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Recommended reading27When he invented SQL, God took him away from wechat8.0Not funny? That's probably because you're not opening it right! The cost of leaving an employee is terrible! SQL to develop these good habits is a fortune MySQL basic knowledge comb and query optimizationCopy the code