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

-- P.S. 如果各位用的是由小排到大的方式,只要把"大於"變"小於"、"DESC"變"ASC"即可

這樣以後使用MySQL排序名次就不會有困擾了

 

   如果覺得對你有幫助的話. 請幫小弟按個讚吧~

 

MySQL相關文章:

   MySQL基本語法(查詢、插入、更新)

   使用MySQL ALTER TABLE 語法修改資料表欄位

   MySQL Temporary Table(臨時表)指令使用與介紹

   使用MySQL時間函數(function)轉換UNIX時間戳記

   Mysql 插入校能優化與測試 

   SQL Injection 範例(登入範例) 

   MySql 異地同步(MySQL Replication)  

 

創作者介紹

史丹利愛碎念

newaurora 發表在 痞客邦 PIXNET 留言(0) 人氣()