簡單記錄幾個有用的sql查詢

iSQlServer發表於2010-10-19
SQL

下面示例中，查詢的資料表參考這一篇的Person表。

一、限制返回的行數

1、Sql Server

 
1 SELECT TOP 10 Id,FirstName, LastName
2 FROM Person

2、Oracle

 
1 SELECT Id,FirstName, LastName
2 FROM Person
3 WHERE ROWNUM<=10

3、DB2

 
1 SELECT Id,FirstName, LastName
2 FROM Person FETCH FIRST 10 ROWS ONLY

4、MySql

 
1 SELECT Id,FirstName, LastName
2 FROM Person LIMIT 10

5、PostgreSQL

 
1 SELECT Id,FirstName, LastName
2 FROM Person LIMIT 10

小結：查詢語句都很基礎，MySql和PostgreSQL的寫法是相同的，可以看到各個DBMS的sql書寫可讀性都不錯，使用者（開發者）使用體驗很重要。

 

二、按特定格式查詢日期（Sql Server版）

實際開發中通常都直接查詢結果，然後用程式語言進行日期格式輸出（如C#、Java等），但是資料庫同樣也提供了這種轉換處理能力，下面整理一下Sql Server的常用時間格式查詢和對應輸出結果：

 
01 SELECT CONVERT(VARCHAR(100), GETDATE(), 0)-- 10 17 2010  4:51PM
02 SELECT CONVERT(VARCHAR(100), GETDATE(), 1)-- 10/17/10 
03 SELECT CONVERT(VARCHAR(100), GETDATE(), 2)-- 10.10.17 
04 SELECT CONVERT(VARCHAR(100), GETDATE(), 3)-- 17/10/10 
05 SELECT CONVERT(VARCHAR(100), GETDATE(), 4)-- 17.10.10
06 SELECT CONVERT(VARCHAR(100), GETDATE(), 5)-- 17-10-10 
07 SELECT CONVERT(VARCHAR(100), GETDATE(), 6)-- 17 10 10 
08 SELECT CONVERT(VARCHAR(100), GETDATE(), 7)-- 10 17, 10 
09 SELECT CONVERT(VARCHAR(100), GETDATE(), 8)-- 16:52:13
10 SELECT CONVERT(VARCHAR(100), GETDATE(), 9)-- 10 17 2010  4:52:13:960PM
11 SELECT CONVERT(VARCHAR(100), GETDATE(), 10)-- 10-17-10 
12 SELECT CONVERT(VARCHAR(100), GETDATE(), 11)-- 10/10/17
13 SELECT CONVERT(VARCHAR(100), GETDATE(), 12)-- 101017
14 SELECT CONVERT(VARCHAR(100), GETDATE(), 13)-- 17 10 2010 16:53:39:403 
15 SELECT CONVERT(VARCHAR(100), GETDATE(), 14)-- 16:53:39:403 
16 SELECT CONVERT(VARCHAR(100), GETDATE(), 20)-- 2010-10-17 16:53:39
17 SELECT CONVERT(VARCHAR(100), GETDATE(), 21)-- 2010-10-17 16:54:55.100 
18 SELECT CONVERT(VARCHAR(100), GETDATE(), 22)-- 10/17/10  4:54:55 PM 
19 SELECT CONVERT(VARCHAR(100), GETDATE(), 24)-- 16:54:55 
20 SELECT CONVERT(VARCHAR(100), GETDATE(), 25)-- 2010-10-17 16:54:55.100 
21 SELECT CONVERT(VARCHAR(100), GETDATE(), 100)-- 10 17 2010  4:54PM 
22 SELECT CONVERT(VARCHAR(100), GETDATE(), 101)-- 10/17/2010
23 SELECT CONVERT(VARCHAR(100), GETDATE(), 102)-- 2010.10.17 
24 SELECT CONVERT(VARCHAR(100), GETDATE(), 103)-- 17/10/2010 
25 SELECT CONVERT(VARCHAR(100), GETDATE(), 104)-- 17.10.2010 
26 SELECT CONVERT(VARCHAR(100), GETDATE(), 105)-- 17-10-2010 
27 SELECT CONVERT(VARCHAR(100), GETDATE(), 106)-- 17 10 2010 
28 SELECT CONVERT(VARCHAR(100), GETDATE(), 107)-- 10 17, 2010 
29 SELECT CONVERT(VARCHAR(100), GETDATE(), 108)-- 16:56:36 
30 SELECT CONVERT(VARCHAR(100), GETDATE(), 109)-- 10 17 2010  4:56:36:370PM 
31 SELECT CONVERT(VARCHAR(100), GETDATE(), 110)-- 10-17-2010
32 SELECT CONVERT(VARCHAR(100), GETDATE(), 111)-- 2010/10/17 
33 SELECT CONVERT(VARCHAR(100), GETDATE(), 112)-- 20101017 
34 SELECT CONVERT(VARCHAR(100), GETDATE(), 113)-- 17 10 2010 16:57:51:713 
35 SELECT CONVERT(VARCHAR(100), GETDATE(), 114)-- 16:59:19:640
36 SELECT CONVERT(VARCHAR(100), GETDATE(), 120)-- 2010-10-17 16:59:19
37 SELECT CONVERT(VARCHAR(100), GETDATE(), 121)-- 2010-10-17 16:59:19.640
38 SELECT CONVERT(VARCHAR(100), GETDATE(), 126)-- 2010-10-17T16:59:19.640
39 SELECT CONVERT(VARCHAR(100), GETDATE(), 130)-- 10 ?? ?????? 1431  4:59:19:640PM

小結：ms真夠貼心的，還有哪種格式它沒有幫我們實現呢？


三、從表中隨機返回n條記錄

1、Sql Server

 
1 SELECT TOP 10 Id, FirstName, LastName
2 FROM Person (NOLOCK)
3 ORDER BY NEWID()

2、Oracle

 
1 SELECT Id, FirstName, LastName FROM(
2     SELECT Id, FirstName, LastName FROM Person
3     ORDER BY DBMS_RANDOM.VALUE()
4 )
5 WHERE ROWNUM<=10

3、DB2

 
1 SELECT Id, FirstName, LastName FROM Person
2 ORDER BY RAND() FETCH  FIRST 10 ROWS ONLY

4、MySql

 
1 SELECT Id, FirstName, LastName FROM Person
2 ORDER BY RAND() LIMIT 10

5、PostgreSQL

 
1 SELECT Id, FirstName, LastName FROM Person
2 ORDER BY RANDOM() LIMIT 10

小結：

（1）、對比各資料庫產品（DBMS）的sql查詢書寫方式，可以發現它們的相似之處都需要使用ORDER BY 子句對行進行隨機排序，而隨機函式都使用各自的內建函式。好玩的地方在於，雖然各個DBMS的隨機函式有相似或相同的地方，但是沒有一個查詢是相同的。

（2）、Oracle的查詢方式相比其他稍顯囉嗦，可讀性稍差，但是可以看出它的思路，理解它的實現原理，對開發者是很有益的。

 

四、將空值轉換為實際值

通常情況下，我們通過case when可以實現空值到實際值的轉換：

 
1 SELECT TOP 10
2 Id,
3 FirstName,
4 CASE 
5 WHEN LastName IS NULL THEN ''
6 ELSE LastName
7 END AS LastName
8 FROM
9 Person (NOLOCK)

但是，更簡潔的寫法是使用COALESCE函式：

 
1 SELECT TOP 10
2     Id,
3     FirstName,
4     COALESCE(LastName,'') AS LastName
5 FROM
6     Person (NOLOCK)

最後，在開發中寫出簡潔高效的sql語句一直是我孜孜追求的目標，每次碰到動輒上百乃至上千行的sql語句或者儲存過程需要維護就頭疼不已，尤其是那些業務邏輯比較複雜的，不知道大家在開發中有沒有過這種經歷。如何規避複雜的sql語句和儲存過程開發易維護的系統，不知道您有什麼看法或者好的解決方案？歡迎討論。

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

相關文章