MySQL 分组排名实现 ROW_NUMBER() OVER (PARTITION BY ORDER BY )

叁歲伎倆 2022-04-01 06:53 549阅读 0赞

Mysql 中因为没有ROWNUM伪列、 那么想要排名、 我们就先要搞出一列伪列、 用作排名

## 伪列为分组排名的**核心内容** ##

SELECT
     (@r :=@r + 1) AS rank
    FROM  (SELECT @r := 0) r 
     ;

![执行结果][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70]

--------------------

# 举个例子： #

## 创建临时表插入测试数据 ##

CREATE TEMPORARY TABLE tm_test (num int , createdat datetime  );
    INSERT INTO tm_test (num, createdat)  VALUES (1, '2018/12/28 21:41:20');
    INSERT INTO tm_test (num, createdat)  VALUES (1, '2018/12/28 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (1, '2018/12/28 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (1, '2018/12/28 21:43:20');
    INSERT INTO tm_test (num, createdat)  VALUES (1, '2018/12/28 21:44:20');
    INSERT INTO tm_test (num, createdat)  VALUES (2, '2018/12/29 21:41:20');
    INSERT INTO tm_test (num, createdat)  VALUES (2, '2018/12/29 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (2, '2018/12/29 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (2, '2018/12/29 21:43:20');
    INSERT INTO tm_test (num, createdat)  VALUES (2, '2018/12/29 21:44:20');
    INSERT INTO tm_test (num, createdat)  VALUES (3, '2018/12/30 21:41:20');
    INSERT INTO tm_test (num, createdat)  VALUES (3, '2018/12/30 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (3, '2018/12/30 21:42:20');
    INSERT INTO tm_test (num, createdat)  VALUES (3, '2018/12/30 21:43:20');
    INSERT INTO tm_test (num, createdat)  VALUES (3, '2018/12/30 21:44:20');
      SELECT * FROM tm_test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 1]

--------------------

## 根据某列顺序排名： ##

SELECT a.*,(@r :=@r + 1) AS rank
    FROM tm_test a,(SELECT @r := 0) r 
    ORDER BY createdat;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 2]

--------------------

## 根据某列顺序排名（相同内容排名一致）： ##

SELECT a.* ,
    CASE 
    WHEN @p=a.createdat THEN @r
    WHEN @p:=a.createdat THEN @r:=@r+1
    END rank
    FROM tm_test a ,(SELECT @r:=0,@p:=NULL)r
    ORDER BY a.createdat;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 3]

--------------------

## 分组排名： ##

SELECT num,createdat,rank
      FROM
      (
     SELECT  a.*,IF(@p=a.num,@r:=@r+1,@r:=1) AS rank,
      @p:= a.num
      FROM tm_test a,(SELECT @p:=NULL,@r:=0)r
      ORDER BY a.num,a.createdat  
      )z;

![在这里插入图片描述][2018122916483975.png]

--------------------

## 分组排名（相同内容排名一致）： ##

SELECT num,createdat,rank
         FROM
      (
       SELECT *,
      IF(@p=num,
          CASE 
             WHEN @s=createdat THEN @r
          WHEN @s:=createdat THEN @r:=@r+1
         END,
         @r:=1 ) AS rank,
        @p:=num,
        @s:=createdat
        FROM tm_test,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
         ORDER BY num,createdat desc
         )z;

![在这里插入图片描述][20181229165513694.png]

--------------------

# 备注： #

多列分组排名

SELECT x.ActionID,x.AuctionID,x.BuyerID,x.Price,x.ActionTime,X.IsDeal
     FROM 
    ( 
      SELECT a.*,IF(@p:=a.AuctionID AND  @x:=a.BuyerID ,@r:=@r+1,@r:=1) AS rank,
     @p:= a.AuctionID, @x:=a.BuyerID  FROM tb_auctionactions a,
      (SELECT @p:=NULL,@z:=NULL, @r:=0)r 
        GROUP BY a.AuctionID,a.BuyerID 
    ORDER BY a.`Price` DESC
      )x
    WHERE x.rank =1

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70]: /images/20220401/fea23c5654794cf4b669092749cc36e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 1]: /images/20220401/927887b0f84d4fa49911e31c85d268dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 2]: /images/20220401/f477053e3aaf483aa876e02f16446305.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 3]: /images/20220401/c3a3ca06371b4c868196c9774ab9d714.png
[2018122916483975.png]: /images/20220401/8fa3a5ae050042578645e36d3c17d3a6.png
[20181229165513694.png]: /images/20220401/0b8d2afad26e4a55bd0bc1c8574f230c.png