常用SQL系列之（九）：日期計算、分頁、跳行與分級等查詢
本系列為@牛旦教育IT課堂在微頭條上釋出的內容，為便於查閱，特輯錄於此，都是常用SQL基本用法。
前兩篇連線：
（一）：SQL點滴（查詢篇）：資料庫基礎查詢案例實戰
（二）：SQL點滴（排序篇）：資料常規排序查詢實戰示例
（三）：常用SQL系列之：記錄疊加、匹配、外連線及笛卡爾等
（四）：常用SQL系列之：Null值、插入方式、預設值及複製等
（五）：常用SQL系列之：多表和禁止插入、批次與特殊更新等
（六）：常用SQL系列之：刪除方式、資料庫、表及索引元資訊查詢等
（七）：常用SQL系列之：表約束、最大/小值、非null數、平均值等
（八）：常用SQL系列之：列值累計、佔比、平均值以及日期運算等
SQL點滴（51）：如何計算兩個日期之間相差的月數或年數？
也就是確定兩個日期間的月份數或年份數，計算其間差。比如計算第一個員工和最後一個員工聘用日期相差的月份數，以及這些月摺合的年數。首先來看——
1）MySQL的實現示例參考：
SELECT
mnth，mnth / 12
FROM
（
SELECT
（YEAR（ max_hd ） - YEAR （ min_hd ））*12 +
MONTH （ max_hd ） - MONTH （ min_hd ） AS mnth
FROM
（ SELECT min（ hiredate ） AS min_hd， max（ hiredate ） AS max_hd FROM employee ） x
） y
注意這個寫法，使用了函式Year和Month為給定的日期返回4位數年份和兩位數月份。這個寫法，也適用DB2。
2）MS SQL中的計算參考：
select datediff（month，min_hd，max_hd），datediff（month，min_hd，max_hd）/12
from（ SELECT min（ hiredate ） AS min_hd， max（ hiredate ） AS max_hd FROM employee ）x
若是Oracle中，其寫法與此類似，只是所有內部函式不同，如下所示：
select months_between（min_hd，max_hd），months_between（min_hd，max_hd）/12
from（ SELECT min（ hiredate ） AS min_hd， max（ hiredate ） AS max_hd FROM employee ）x
這種計算，主要要想清楚他們的關係機理，你可以分別顯示年份和月份以作對比，比如：
SELECT
YEAR（ max_hd ）， YEAR（ min_hd ），
MONTH （ max_hd ），MONTH （ min_hd ）
FROM
（ SELECT min（ hiredate ） AS min_hd， max（ hiredate ） AS max_hd FROM employee ） x
好了，動手試試吧。
SQL點滴（52）：如何實現對查詢結果集的分頁操作？
進一步講，就是給查詢結果分頁，或者“滾動顯示”所查詢的結果。比如針對人員資訊表有500條記錄，希望每次顯示10行，那麼醬油50頁可以“翻閱”，從第1頁到第50頁，在操作頁面上，體現為1-50頁，可以一頁頁“點選翻頁”，順序檢視每一頁，如何在SQL中實現呢？首先，我們來看：
1）在MySQL中的實現：
select id，col01，col02，col03，coln from sometable order by id limit 10 offset 0
上面的意思為我們以id為主鍵排序 ，第一次從第一條記錄開始，一次檢視前10條記錄。需要注意的是，關鍵字limit 後的數值，指定要檢視多少記錄，offerset定義了返回結果的第一條記錄的偏移位置（就是從排序集中哪條記錄開始依次返回10條記錄，或跳過哪些記錄），如果體現在頁面上，那麼offer 0可以看做從頭顯示第一頁10條記錄，那麼第二頁的偏移量offset就是從（2-1）*10，以此類推。當然，具體返回那些欄位，根據需要指定。
這種SQL的寫法，也適合PostgreSQL。
2）在Oracle中的實現：
select col1，col2，coln
from（
select row_number（） over （order by id） as rn，col1，col2，coln from sometable
） x where rn between 1 and 10 。
在where子句的between 和and後的數字，就是表示要返回從那條記錄到哪條記錄的返回值返回。
此寫法同樣適用DB2和MS SQL Server。row_number（）給每行記錄非配唯一序號，以便可以明確返回需要的頁記錄。
另外，對於Oracle，還有另外一種 方式，使用rownum來生成唯一記錄號。
Oracle的ROWNUM的具體SQL實現，自己動手實現一下吧。
SQL點滴（53）：小花招—怎樣使用SQL語句來跳過表中的指定n行返回所有結果？
比如說，從employee表中每隔一行返回一名員工，或者查詢第1、3、5等以此類推的記錄，如何實現？其實這個實現可以基於我前面介紹的翻頁模式來實現。具體來參考如下實現：
1）MySQL間隔模式返回結果：
SELECT
x。ename
FROM
（
SELECT
a。ename，
（ SELECT count（ * ） FROM employee b WHERE b。ename <= a。ename ） AS rn
FROM
employee a
） x
WHERE
MOD （ x。rn， 2 ） =1
由於MySQL沒有為行分等級或給行編號的函式，所以需要用子查詢給行分順序等級（作為行編號，本查詢中用名字來進行分級實現記錄的行編號），然後使用求模操作來跳過行。
上面的SQL語句也適用PostgreSQL。
2）在Oracle中的實現：
select ename from （
select row_number（） over （order by ename） rn，ename from employee
） x
where mod（rn，2） = 1
關於求模，上面的寫法也適用DB2，若在MS SQL中，則用%運算子。上面的語句將跳過偶數行號。
自己動手試試吧。
SQL點滴（54）：如何查詢指定部門的員工和部門名稱資訊，而其餘部門只顯示部門名稱而無員工資訊？
比如說，只返回部門編號為2和3的部門員工資訊以及部門資訊，而編號為1和4的只返回部門資訊，如何實現。其實，這個用外連線並在連線中用or邏輯即可。參考SQL如下：
SELECTe。ename，d。dpid，d。dpname，d。dpaddressFROMdepartment dLEFT JOIN employee e ON （ d。dpid = e。departid AND （ e。departid = 2 OR e。departid = 3 ） ）ORDER BY 1
注意，order by 1，是指按ename進行預設升序排序，如果是2，則指定位dpid升序排序。
這個寫法適合MySQL、DB2、PostgreSQL以及SQL Server。也適用Oracle9i及以上版本。
另外，上面的語句還可以用e。departid進行篩選，然後進行外部連結，如下所示：
SELECT
e。ename，
d。dpid，
d。dpname，
d。dpaddress
FROM
department d
LEFT JOIN
（ select ename，departid from employee where departid in（2，3）
） e on （e。departid = d。dpid ）
order by 1
自己動手試試吧。如果有錯，記得找找原因哦 ^_^
SQL點滴（55）：如何透過查詢語句為返回結果分級並返回感興趣的結果？
比如說，對employee表的工資進行分級，然後返回想要的等級的相關資料。比如薪資一樣的為同級別，不同的為不同級別。如果有10條記錄，那分出來級別是小於等於10的。
在MySQL中，我使用了子查詢，為每個工資建立了一個等級，然後利用等級做條件返回結果，參考語句如下：
第一步，可以查分級結果；
SELECT
（ SELECT count（ DISTINCT b。salary ） FROM employee b WHERE a。salary <= b。salary ） AS rnk，
a。salary，
a。ename
FROM
employee a order by 1
第二步，把分級結果作為查詢物件，新增where子句來返回感興趣的結果：
select * from x where rnk<=3
x指代第一步的查詢語句（作為第二步的子查詢），試試吧。我這裡演示的兩步結果如圖所示。
另外，可以看看本號@牛旦教育IT課堂 中關於
JPA學習文章
：系列總結：JPA核心介面綜合實戰案例
