mysql分组求最大ID记录行方法

##创建表

CREATE TABLE `test_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`order_no` BIGINT(20) DEFAULT NULL,
`amt` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

 

##插入测试数据

INSERT INTO `test_user`(`id`,`order_no`,`amt`)
VALUES
(1,111,100),
(2,222,200),
(3,333,300),
(4,111,101),
(5,111,102),
(6,222,201),
(7,222,202),
(8,333,301),
(9,333,302);

 

##DEMO1:找出最大的金额

SELECT id,order_no,MAX(amt) FROM `test_user`
GROUP BY order_no
/**
期望结果：
"id" "order_no" "max(amt)"
"1" "111" "102"
"2" "222" "202"
"3" "333" "302"
**/

 

##DEMO2:根据每个订单号找出ID最大的一行记录

/**
期望结果：
"id" "order_no" "amt"
"5" "111" "102"
"7" "222" "202"
"9" "333" "302"
**/

 

##ID最小的这一行，不符合要求，以下5个方法。

SELECT * FROM `test_user` GROUP BY order_no

 

##简单方法：倒序然后再分组，可以查询出来。

SELECT t.* FROM (
SELECT * FROM `test_user` ORDER BY id DESC
) t GROUP BY order_no

 

##找出最大的这一条记录数
##方法1 根据ID来关联

SELECT * FROM `test_user` t
WHERE id = (SELECT MAX(id) FROM test_user WHERE order_no = t.order_no);

 

##方法2 根据中间表的订单号和max ID来关联

SELECT t.* FROM `test_user` t,
(SELECT order_no,MAX(id) maxId FROM `test_user` GROUP BY order_no) t2
WHERE t.order_no = t2.order_no AND t.id=t2.maxId;

 

##方法3 NOT EXISTS

SELECT * FROM `test_user` t
WHERE NOT EXISTS (SELECT 1 FROM test_user WHERE order_no = t.order_no AND id > t.`id`);

 

##方法4 根据LEFT JOIN的订单号和max ID来关联 (方法2的另外一种表现形式)

SELECT t.* FROM `test_user` t
INNER JOIN (SELECT order_no,MAX(id) maxId FROM `test_user` GROUP BY order_no) t2
ON t.order_no = t2.order_no AND t.id=t2.maxId;

 

##方法5 (方法3的另外一种表现形式) 查询count为0的情况

SELECT * FROM `test_user` t
WHERE 1 > (SELECT COUNT(1) FROM test_user WHERE order_no = t.order_no AND id > t.`id`);