MySQL分组排序（取第一或最后）

 

MySQL分组排序（取第一或最后）

 

 方法一：速度非常慢，跑了30分钟

SELECT
    custid,
    apply_date,
    rejectrule 
FROM
    (
    SELECT
        *,
    IF
        ( @pkey <> custid, @rank := 1, @rank := @rank + 1 ) AS rank,
        @pkey := custid 
    FROM
        (
        SELECT
            custid,
            createTime,
            SUBSTR( createTime, 1, 10 ) AS apply_date,
            rejectRule,
        STATUS 
        FROM
            (
            SELECT
                * 
            FROM
                credit.`apply` 
            WHERE
                SUBSTR( createTime, 1, 10 ) >= '2019-10-26' 
                AND custid <> "" 
                AND SUBSTR( custid, 1, 2 ) = '10' 
            ) t1,
            ( SELECT @pkey := 0, @rank = 0 ) t2 
        ) t 
    ORDER BY
        custid,
        createTime DESC 
    ) a 
HAVING
    rank = 1 
    AND STATUS <> 1 

    LIMIT 100;

方法二：非常快，约为1分钟

SELECT
    custid,
    apply_date,
    rejectrule ,
    STATUS
FROM
    (
    SELECT
        custid,
        apply_date,
        rejectrule,
        STATUS 
    FROM
        (
        SELECT
            custid,
        CASE    
                WHEN locate( '|', statuss ) > 0 THEN
                SUBSTR( statuss, 1, INSTR( statuss, '|' )- 1 ) ELSE statuss 
            END AS STATUS,
        CASE    
                WHEN locate( '|', apply_dates ) > 0 THEN
                SUBSTR( SUBSTR( apply_dates, 1, INSTR( apply_dates, '|' )- 1 ), 1, 10 ) ELSE SUBSTR( apply_dates, 1, 10 ) 
            END AS apply_date,
        CASE      
                WHEN locate( '|', rejectrules ) > 0 THEN
                SUBSTR( rejectrules, 1, INSTR( rejectrules, '|' )- 1 ) ELSE rejectrules 
            END AS rejectrule 
        FROM
            (
            SELECT
                custid,
                group_concat( STATUS ORDER BY createTime DESC SEPARATOR '|' ) AS statuss,
                group_concat( SUBSTR( createTime, 1, 10 ) ORDER BY createTime DESC SEPARATOR '|' ) AS apply_dates,
                group_concat( rejectrule ORDER BY createTime DESC SEPARATOR '|' ) AS rejectrules 
            FROM
                credit.`apply` 
            WHERE
                SUBSTR( createTime, 1, 10 ) >= '2019-07-26' 
                AND custid <> "" 
                AND SUBSTR( custid, 1, 2 ) = '10' 
            GROUP BY
                custid 
            ) t 
        ) tt 
    ) b 
WHERE
    apply_date >= '2019-10-12' 
    AND STATUS <> 1 LIMIT 100;

因为可能数据很多group_concat（）可能会超长，解决方案见博客

方法三：

MySQL新版本已经支持窗口函数：mysql8.0

select custid,createTime,status,rejectrule
(
SELECT RANK() OVER (PARTITION BY custid ORDER BY createTime desc ) AS rank1, 
  custid, 
  createTime ,
  status,
  rejectrule
FROM credit.apply
) T 
where rank1=1;