mysql 分组排序并加序号

mysql 分组排序并加序号。

1.需要在已分好组的数据外,嵌套一层查询。这层查询只用来做小组的编号,或者是order by,序号生成是在Order by 之后(这点很重要)。

2.加上绿色背景部分

3.相同的package_item_id,进行小组排序。

SELECT 
 @r:= CASE WHEN @rank=te.package_item_id THEN @r+1 ELSE 1 END AS quoteRanking,
		@rank:=te.package_item_id AS TYPE,
		te.* FROM (
  SELECT 

    pqi.item_Name itemName,
    pqi.`package_item_id`,
    pt.bidder_Name_Cn bidderName,
    pt.`bidder_id`,
    ROUND(MAX( pqi.unit_Price_Taxed), 2) quotePrice,
    DATE_FORMAT(
      pqi.create_Time,
      '%Y-%m-%d %H:%i:%s'
    ) quoteTime,
    pqi.price_Unit priceUnit,
    pqi.price_Currency priceCurrency 
  FROM
    ptb_quote pt,
    ptb_quote_item pqi 
  WHERE pt.id = pqi.quote_id 
    AND pt.package_id = '10937' 
    AND pqi.unit_Price_Taxed IS NOT NULL 
    AND pt.is_Deleted = '0' 
    AND pqi.is_Deleted = '0'
GROUP BY pqi.package_item_id,
  pt.bidder_id 
ORDER BY pqi.package_item_id,
  pqi.unit_Price_Taxed DESC ) te,(SELECT @r:=0 ,@rank:='') b
  ORDER BY te.package_item_id,
  te.quotePrice DESC   

  效果图

 

posted @ 2019-04-03 20:28  张立轩  阅读(10022)  评论(0编辑  收藏  举报