MySQL获取每组多字段按顺序最大值的记录

需求：

        将每个用户生效日期最大的一条数据变为有效，相同用户相同生效日期时，获取修改时间最大的数据

测试用表结构

CREATE TABLE `test_table` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`account` VARCHAR(50) NOT NULL COMMENT '用户id',
	`take` INT(11) NOT NULL DEFAULT '0' COMMENT '生效日期',
	`update` INT(11) NOT NULL DEFAULT '0' COMMENT '修改日期',
	`status` INT(11) NOT NULL DEFAULT '0' COMMENT '状态',
	PRIMARY KEY (`id`)
)
COMMENT='测试'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

测试数据：

INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (1, 'a', 3, 2, 0);
INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (2, 'a', 3, 3, 0);
INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (3, 'a', 2, 3, 0);
INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (4, 'b', 3, 2, 0);
INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (5, 'b', 3, 3, 0);
INSERT INTO `test_table` (`id`, `account`, `take`, `update`, `status`) VALUES (6, 'b', 2, 2, 0);

1、先查询每个用户需要修改为有效的数据(通过left join方式)

        

SELECT t1.* FROM 
test_table t1
LEFT JOIN test_table t2 ON t1.ACCOUNT = t2.account AND t1.take < t2.take
LEFT JOIN test_table t3 ON t1.ACCOUNT = t3.account AND t1.take = t3.take AND t1.update < t3.update
WHERE t2.account IS NULL AND t3.account IS null

 查询思路

        当t1的take处于最大值时，没有t2.take更大的值，因此对应的t2.account值为null；

        当t1.take=t3.take时，t1.update处于最大值时，没有t3.update更大的值，因此对应的t3.account值为null

2、通过窗口函数查询

WITH s2 AS (
	WITH s1 AS (
	    SELECT id,ACCOUNT,take,`update`,RANK() OVER(PARTITION BY ACCOUNT
	    ORDER BY take DESC) AS `rank`
	    FROM test_table 
	)
	SELECT *,RANK() OVER(PARTITION BY ACCOUNT
	ORDER BY `update` DESC) AS `updateRank`
	FROM s1
	WHERE `rank` = 1)
SELECT *
FROM s2
WHERE updateRank = 1

查询思路

        先查询相同account下take最大的数据 再查询相同account、take下 update 最大的数据

参考文档

        https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html

修改语句

        

UPDATE test_table SET `status`='1' WHERE  `id` IN (
SELECT id FROM (
	SELECT t1.id FROM 
    test_table t1
    LEFT JOIN test_table t2 ON t1.ACCOUNT = t2.account AND t1.take < t2.take
    LEFT JOIN test_table t3 ON t1.ACCOUNT = t3.account AND t1.take = t3.take AND t1.update < t3.update
    WHERE t2.account IS NULL AND t3.account IS null
    )t
);