SQL的几个好的应用

原文网址：http://jcobra.blog.163.com/blog/static/47049320071924220107/

SQL的几个好的应用

【数据库】 2007-02-09 14:42:20 阅读57 评论0   字号：大中小 订阅

一、排序：

    列出每一行的排名是一個常見的需求，可惜 SQL 並沒有一個很直接的方式達到這個需求。要以 SQL 列出排名，基本的概念是要做一個表格自我連結 (self join)，將結果依序列出，然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑，所以最好的方式是用一個實例來介紹。假設我們有以下的表格：

Total_Sales 表格

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

要找出每一行的排名，我們就打入以下的 SQL 語句：

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

結果:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料，那這部分的 WHERE 子句本身就可以產生出正確的排名。

子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料)，仍然能夠產生正確的排名。

 

二、中位数（排序后找出位于中间的值）

要算出中位數，我們必須要能夠達成以下幾個目標：

• 將資料依序排出，並找出每一行資料的排名。
• 找出『中間』的排名為何。舉例來說，如果總共有 9 筆資料，那中間排名就是 5 (有 4 筆資料比第 5 筆資料大，有 4 筆資料比第 5 筆資料小)。
• 找出中間排名資料的值。

來看看以下的例子。假設我們有以下的表格：

Total_Sales 表格

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

要找出中位數，我們就鍵入：

SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

結果:

Median

20

讀者將會發現，第 2 行到第 6 行是跟產生 排名 的語句完全一樣。第 7 行則是算出中間的排名。DIV 是在 MySQL 中算出商的方式。在不同的資料庫中會有不同的方式求商。第 1 行則是列出排名中間的資料值。