MySQL排序名次、排行(使用SQL語法排序)－史丹利愛碎念

MySQL排序名次、排行(使用SQL語法排序)

MySQL的排序語法相當容易，但如果用MySQL來排次序(名次)的話，就需要用點心機，最近剛好有個使用MySQL語法計算名次的需求，分享給大家參考參考

-- `value_range`這張表紀錄的是商品每天的價格，下面簡單示範如何依據價格由大到小用SQL語法排序

CREATE TABLE `value_range` (
`item_id` int(10) NOT NULL COMMENT '商品編號',
`data_date` date NOT NULL COMMENT '日期',
`price` decimal(10,4) DEFAULT NULL COMMENT '商品價格',
PRIMARY KEY (`item_id`,`data_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

-- 就單純對數字大小作排序，我們只需要加上ORDER BY 並指定欄位即可，下列範例針對 price 欄位做排序

SELECT * FROM value_range ORDER BY price DESC

-- 註：DESC 代表該欄位數字由大至小排序；ASC 則是由小到大做排序

-- 如果除了排出大小之外，還需要用SQL語法排出名次的話，就需要下面這種方法，結果如下圖

SELECT v.rank, v.data_date, v.nowPrice FROM
(SELECT data_date, @pre_price := @price AS pre_price, @price := price AS nowPrice, @rank := IF(@pre_price = @price, @rank, @rank + 1) AS rank FROM value_range WHERE item_id = 2885 ORDER BY price DESC) AS v,
(SELECT @pre_price := 0, @price := 0, @rank := 0) AS x;

sql語法排序_1.png

-- 乍看之下有點眼花撩亂，現在就一步一步來拆解吧～

-- 下面這行代表，先宣告一開始的全域變數為0，分別用來放上一筆價格、目前的價格、目前的次序)

(SELECT @pre_price := 0, @price := 0, @rank := 0)

-- 把上一筆取到的價格(@price)放入 @pre_price，再把這次取到的 price 放入 @price；最後比對上一筆資料的price和這筆資料的price是否相同，如果相同則排序不變，如果不同則名次+1

SELECT data_date, price, @pre_price := @price AS pre_price, @price := price AS nowPrice, @rank := IF(@pre_price = @price, @rank, @rank + 1) AS rank

-- P.S. 指定變數值的方法是有順序性的，不能任意調換

-- 上面的例子看到同樣是275元有2天並列第2，但274元的排序卻是第3，如果這樣的排序，是跟獲獎的名額、或是積分排行有關的話，肯定引起不少客訴
-- 我們再進一步優化一下，增加兩個變數 @tempCnt、@cnt來計算重複的價格數量

-- 比對上一筆資料的price和這筆資料的price是否相同，如果相同則排序不變，如果不同則將名次加上上一筆相同價格的數量

@rank := IF(@pre_price = @price, @rank, @rank + @tempCnt)

-- 比對上一筆資料的price和這筆資料的price是否相同，如果相同則數量+1，如果不同則數量為1

@cnt := IF(@pre_price = @price, @cnt + 1, 1)

-- 比對上一筆資料的price是否大於這筆資料的price，如果大於則數量為1，如果不是大於則數量為剛剛計算完的數量@cnt

@tempCnt := IF(@pre_price > @price, 1, @cnt)

SELECT v.data_date, v.nowPrice, v.rank FROM
(SELECT data_date, @pre_price := @price AS pre_price, @price := price AS nowPrice, @rank := IF(@pre_price = @price, @rank, @rank + @tempCnt) AS rank,
@cnt := IF(@pre_price = @price, @cnt + 1, 1) AS cnt, @tempCnt := IF(@pre_price > @price, 1, @cnt) AS rankCnt FROM value_range
WHERE item_id = 2885 ORDER BY price DESC) AS v,
(SELECT @pre_price := 0, @price := 0, @rank := 1, @tempCnt := 0, @cnt := 0) AS x;

sql語法排序_2.png