Mysql 分组后获取最新一条记录

目录

脚本

基于id自增

基于createTime

在限制条件下基于子查询方式查询

---

脚本

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `action` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO  `t_user`(`id`, `name`, `action`, `create_time`) VALUES (1, '张三', '技能1', '2023-02-28 01:01:01');
INSERT INTO  `t_user`(`id`, `name`, `action`, `create_time`) VALUES (2, '张三', '技能2', '2023-02-28 02:02:02');
INSERT INTO  `t_user`(`id`, `name`, `action`, `create_time`) VALUES (3, '李四', '技能1', '2023-02-28 01:02:01');
INSERT INTO `t_user`(`id`, `name`, `action`, `create_time`) VALUES (4, '李四', '技能3', '2023-02-28 02:02:03');

基于id自增

当id是自增时

select * from t_user where id  in(select max(id) from t_user GROUP BY `name`);

基于createTime

SELECT
	t.id,
	t.name,
	t.create_time 
FROM
t_user t
INNER JOIN  ( SELECT name, max(create_time) AS create_time FROM t_user GROUP BY `name`) t2 ON t.name= t2.name
and t.create_time = t2.create_time;

对name字段分组后,得到name + max(create_time)，利用关联查询,关联原表与子表，利用name 、create_time两个条件锁定记录。

在限制条件下基于子查询方式查询

需求:查询本渠道下本月首次出现且之前未出现的金额大于500的记录
-- 根据user_id分组,获取最早出现的那条记录的create_time,再自关联+内连接根据user_id+create_time查询最早的那条完整记录,最后筛选未在之前出现过,用not in逻辑

SELECT * FROM (SELECT * FROM table_01 t1 WHERE channel_code = 'channe_01' AND STATUS IN ( 1 )
    AND finish_time >='2023-08-01 00:00:00'
		AND finish_time <'2023-09-01 00:00:00') t1
    INNER JOIN (
    SELECT
    user_id,
    min(create_time) AS create_time 
    FROM
    table_01
    WHERE
    channel_code = 'channe_01'
    AND STATUS IN (1)
    AND finish_time >='2023-08-01 00:00:00'
    AND finish_time  <'2023-09-01 00:00:00'
    GROUP BY
    user_id
    ) t2
    ON t1.user_id = t2.user_id
    AND t1.create_time = t2.create_time
    WHERE
    t1.user_id NOT IN ( SELECT user_id FROM table_01 WHERE channel_code = 'channe_01' AND STATUS IN (1) AND finish_time  <'2023-08-01 00:00:00')
    AND t1.amount > 500;