高級查詢

　　本篇文章示例介紹的查詢操作不同於其他查詢操作，它們體現了不同的查詢思路，需要以每次一頁的方式顯示結果集。

 

1.在結果集中翻頁

　　問題：返回員工表中薪水排名前五的員工，然後再返回接下來排名前五的員工。

　　解決方案：這個問題看起來很簡單，只需要根據「薪水」這一列進行倒序排序，然後分頁操作即可。但是如果存在重複數據時就會有問題。

　　先按照「薪水」這一列進行倒序排序：

　　

 

　　然後分頁取排名前五的數據：

　　

 

 

 　　看似沒問題，但是題目要求返回的是薪水排名前五的數據。因為這裡有薪水相同的數據，返回的數據其實不止五條。應該返回下面框起來的數據：

　　

 

 

 　　遇到這種情況，需要使用窗函數 rank() over 給每條數據進行排名（當前行在其分區內的排名，有間隙。要無間隙可以使用 dense_rank 。不使用ROW_NUMBER() ，該函數不區分使用並列），然後使用 between and 語法分頁。

select * from (
SELECT *,rank() over(order by SAL desc) as rn FROM test.emps ) a where rn between 1 and 5

　　

 

 

 

2.在表中跳過 n 行數據

　　問題：以每次跳過一人的方式返回員工表中的數據。換言之，想返回第一個員工，第三個員工 ......

　　解決方案：先對結果集排序，否則不存在「第一個」，「第二個」，「下一個」的概念。然後使用窗函數 ROW_NUMBER() 給每一行進行編號，再結合這些編號和求模函數 mod() 跳過不想返回的行。

select *,mod(rn,2) from (
select *,row_number() over(order by id) as rn from emps ) a
where mod(rn,2) = 1

　　

 

 

 

3.確定哪些行是互逆的

　　問題：有一張包含兩次考試結果的表，想確定哪兩組成績是互逆的。

　　

 

 

 　　從這些數據可知，Test1 為70、Test2 為90與Test1 為90、Test2 為70 是互逆的。Test1 為20、Test2 為20與Test1 為20、Test2 為20 是互逆的。希望返回如下結果：

　　

 

 

 　 解決方案：使用自連接找出這樣的行，即一行的 Test1 和Test2 與 另一行的 Test2和Test1 相等。然後去重。

SELECT a.test1,a.test2 FROM test.grades a
join test.grades b on a.test1=b.test2 and a.test2 = b.test1
where a.id < b.id

　　這裡使用 a.id < b.id 去重。

 

4.平移行值

　　問題：返回每位員工的姓名，薪水，下一個更高和更低的薪水值。如果沒有更高或更低的薪水值，就執行迴轉操作。即如果當前員工的薪水是最低的，則將它的下一個更低的薪水值設置為最高的薪水；如果當前員工的薪水是最高的，就將它的下一個更高的薪水設置為最低的薪水。

　　解決方案：使用窗函數 Lead over 和 Lag over 可以輕鬆取到前一行和後一行數據。然後使用 min 和 max 獲取最小和最大值。

　　第一步：取下一行數據的薪水作為當前行的下一個更高的薪水，取上一行的薪水作為下一個更低薪水

select ename,sal,lead(sal) over(order by sal) 下一個更高的薪水,lag(sal) over(order by sal) 下一個更低薪水 from emps

　　

 

 

 　　第二步：處理第一行和最後一行

select ename,sal,
coalesce( lead(sal) over(order by sal),min(sal) over()) 下一個更高的薪水,
coalesce( lag(sal) over(order by sal),max(sal) over()) 下一個更低薪水 from emps

　　

 

 

 

5.消除重複行

　　問題：找出員工表中不同的部門編號，但不想看到重複的行。

　　解決方案：最簡單的方法是使用 distinct 關鍵字或者使用 group by 。另一種解決方法是，使用窗函數 row_number over 。

select * from (
select DeptNo,row_number() over(partition by  DeptNo order by deptno) as rn  from emps ) a
where rn = 1

　　row_number() over(partition by  DeptNo order by deptno) as rn 根據部門編號分區，給分區內每一行進行編號。然後取分區內第一行。

　　

 

 

 

6.查找馬值

　　問題：返回一個結果集，包含員工名字、部門編號、薪水、獲聘日期以及所屬部門最後聘請的員工的薪水。

　　　　所屬部門最後聘請的員工的薪水就是馬值，因為查找這些值的方法與國際象棋中馬的走法類似。先跳到某一行，然後跳到某一列。同樣，要找到所屬部門最後聘請的員工的薪水，必須先定位到最後獲聘日期所在的行，然後獲取該行的薪水值。

　　解決方案：使用  max(HireDate) over(partition by DeptNo)  和 case when 語法找出所屬部門最後聘請的員工。再次使用 Max Over 獲取所屬部門最後聘請的員工的薪水值。

select DeptNo,Ename,Sal,HireDate, max(Lastest_Sal) over(partition by DeptNo) from (
select DeptNo,Ename,Sal,HireDate,
case  max(HireDate) over(partition by DeptNo) 
    when HireDate then SAL else 0 
    end  as Lastest_Sal
    from emps ) a

 

7.生成簡單預測

　　問題：根據當前的數據返回表示未來行動的行和列。例如，下面有三行訂單數據：

　　

 

 　　每行數據包含下單日期和處理日期（下單兩天後）。現在想對每行數據都返回三行（當前行和另外兩行），還想返回另外兩列，訂單核驗日期（訂單處理一天後）和發貨日期（訂單核驗一天後）。並且想返回一個呈現整個過程的結果集，下圖的樣子：

　　

 

 　　

　　解決方案：使用笛卡爾積為每行訂單再生成兩行樹，然後使用 Case 表達式來創建所需的列值。

with recursive t3 (n) as
(
    select 1 as n
    union all
    select n+1 from t3 where n< 3
)


select id, OrderDate 下單日期,processDate 處理日期, 
case when n >= 2 then adddate(OrderDate,interval 1 day)
    else '' end as 核驗日期,
case when n>2 then adddate(OrderDate,interval 2 day)
    else '' end as 發貨日期
 from orders o
join t3

 

Tags:

VirMach 便宜 VPS