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 ) = ''
) 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 ) = ''
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;