Mysql将查询后的数据进行排名的SQL语句

数据测试表

表名TEST

需求:这是一张店铺的虚拟表,ID为店铺ID,USER_ID为该店铺的发展人,现需要对USER_ID进行分组查询每个USER下共有多少家店铺,并对这个数据进行排名


思路:有点类似循环里面的自增一样,设置一个变量并赋予初始值,循环一次自增加1,从而实现排序;

   mysql里则是需要先将数据查询出来并先行按照需要排序的字段做好降序desc,或则升序asc,设置好排序的变量(初始值为0):

   a>.将已经排序好的数据从第一条依次取出来,取一条就自增加一,实现从1到最后的一个排名

   b>.当出现相同的数据时,排名保持不变,此时则需要再设置一个变量,用来记录上一条数据的值,跟当前数据的值进行对比,如果相同,则排名不变,不相同则排名自增加1

   c.当出现相同的数据时,排名保持不变,但是保持不变的排名依旧会占用一个位置,也就是类似于(1,2,2,2,5)这种排名就是属于中间的三个排名是一样的,但是第五个排名按照上面一种情况是(1,2,2,2,3),现在则是排名相同也会占据排名的位置


首先,我们先要把进行排名前的数据查询出来,这很简单

  1. <span style="font-size:18px;">SELECT  
  2.     USER_ID,  
  3.     COUNT(*) as QUANTITY  
  4. FROM  
  5.     TEST  
  6. GROUP BY  
  7.     USER_ID  
  8. ORDER BY  
  9.     QUANTITY</span>  
结果如下:

下面,就需要对上面的数据进行排名了

现在有三种排序方法,依次来举例

一、不管数据相同与否,排名依次排序(1,2,3,4,5,6,7.....)

  1. SELECT  
  2.     a.USER_ID,  
  3.     a.QUANTITY,  
  4.     @rownum := @rownum + 1 AS RANK  
  5. FROM  
  6.     (  
  7.         SELECT  
  8.             USER_ID,  
  9.             COUNT(*) AS QUANTITY  
  10.         FROM  
  11.             TEST  
  12.         GROUP BY  
  13.             USER_ID  
  14.         ORDER BY  
  15.             QUANTITY  
  16.     ) AS a,  
  17.     (SELECT @rownum := 0) r  

结果如下:


这样子算是排序完成了,但是还会有一个缺点,拿上面数据来说,前两条和最后两条数据的quantity值相等,谁在前谁在后就不好说了,所以有了下面的方法


二、只要数据有相同的排名就一样,排名依次排序(1,2,2,3,3,4,5.....)

  1. SELECT  
  2.     a.USER_ID,  
  3.     a.QUANTITY,  
  4. CASE  
  5. WHEN @rowtotal = a.QUANTITY THEN  
  6.     @rownum  
  7. WHEN @rowtotal := a.QUANTITY THEN  
  8.     @rownum :=@rownum + 1  
  9. WHEN @rowtotal = 0 THEN  
  10.     @rownum :=@rownum + 1  
  11. END AS RANK  
  12. FROM  
  13.     (  
  14.         SELECT  
  15.             USER_ID,  
  16.             COUNT(*) AS QUANTITY  
  17.         FROM  
  18.             TEST  
  19.         GROUP BY  
  20.             USER_ID  
  21.         ORDER BY  
  22.             QUANTITY  
  23.     ) AS a,  
  24. (SELECT @rownum := 0 ,@rowtotal := NULL) r  

这时候就新增加了一个变量,用于记录上一条数据的记录了,只要当前数据记录跟上一条数据的记录比较,相同数量的排名就不变,不相同数量的排名就加一,并且更新变量的分数值为该条数据的分数,依次比较


结果如下:



如果你需要数量相同的排名也相同,但是后面的排名不能受到数量相同排名相同而不占位的影响,也就是哪怕你排名相同,你也占了这个位置(比如:1,2,2,4,5,5,7....这种形式的,虽然排名有相同,但是你占位了,后续的排名根据占位来排)于是


三、只要数据有相同的排名就一样,但是相同排名也占位,排名依次排序(1,2,2,4,5,5,7.....)

  1. SELECT  
  2.     new.USER_ID,  
  3.     new.QUANTITY,  
  4.     new.RANK  
  5. FROM  
  6.     (  
  7.         SELECT  
  8.             a.USER_ID,  
  9.             a.QUANTITY,  
  10.             @rownum := @rownum + 1 AS num_tmp,  
  11.             @incrnum := CASE  
  12.         WHEN @rowtotal = a.QUANTITY THEN  
  13.             @incrnum  
  14.         WHEN @rowtotal := a.QUANTITY THEN  
  15.             @rownum  
  16.         END AS RANK  
  17.         FROM  
  18.             (  
  19.                 SELECT  
  20.                     USER_ID,  
  21.                     COUNT(*) AS QUANTITY  
  22.                 FROM  
  23.                     TEST  
  24.                 GROUP BY  
  25.                     USER_ID  
  26.                 ORDER BY  
  27.                     QUANTITY  
  28.             ) AS a,  
  29.             (  
  30.                 SELECT  
  31.                     @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0  
  32.             ) r  
  33.     ) AS new  

 结果如下:



方法介绍完了,最后把三个排序结果放在一起对比一下



阅读更多
文章标签: Mysql
个人分类: Mysql
上一篇MYSQL日期 字符串 时间戳互转
下一篇MySQL case when 使用
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭