幾個SQL查詢小技巧

張哥說技術發表於2022-12-21
SQL


1、行列轉換：

問題：假設有張學生成績表(tb)如下:

幾個SQL查詢小技巧

想變成(得到如下結果)： 

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


2、分頁

方案一：利用NOT IN和SELECT TOP分頁語句形式


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


 方案二：利用ID大於多少和SELECT TOP分頁語句形式

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


方案三：利用SQL Server中的特性ROW_NUMBER進行分頁 

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


3、結果合併

合併重複行


 SELECT * FROM A
UNION
SELECT * FROM B


不合並重復行

SELECT * FROM A
UNION ALL
SELECT * FROM B


4、隨機排序

SELECT * FROM TestTable ORDER BY NEWID()


還可以結合TOP取隨機的前N條記錄

SELECT TOP 100 * FROM TestTable ORDER BY NEWID()


5、以任意符號分隔取兩邊資料

例如我們以逗號(,)來分割資料，將如下資料

幾個SQL查詢小技巧

分割成如下圖所示：

幾個SQL查詢小技巧


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


最後一步我們將'A,B'拆分出來了B，同理A我們也可以用類似的方法獲取到。


6、WAITFOR延時執行

例 等待1 小時2 分零3 秒後才執行SELECT 語句


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


其中 DELAY是在延時多長時間後才開始執行。

例 等到晚上11 點零8 分後才執行SELECT 語句


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


其中TIME是等到具體某個時刻才開始執行

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024923/viewspace-2928892/，如需轉載，請註明出處，否則將追究法律責任。

相關文章