获取数据表分组的最大值所在行所有信息的方法

业务系统中经常会有一些统计，比如对商品销量和销售额进行汇总统计，并且获取销量最大的商品，是哪个业务员销售，或则哪个月份最高等等，这样就需要明确知道最大值所在行或者ID，这里提供两种方法进行统计:

首先创建基础数据，如下:

DROP TABLE orders;


CREATE TABLE orders
(id VARCHAR(10),
statu CHAR(1),
goods_id VARCHAR(10),
price DECIMAL(12,2)
)

DELETE FROM orders

INSERT INTO orders(id,goods_id,statu,price) VALUES('1','黄金','1',100);
INSERT INTO orders(id,goods_id,statu,price) VALUES('2','黄金','1',200);
INSERT INTO orders(id,goods_id,statu,price) VALUES('3','黄金','0',300);
INSERT INTO orders(id,goods_id,statu,price) VALUES('4','黄金','1',400);
INSERT INTO orders(id,goods_id,statu,price) VALUES('5','钞票','0',150);
INSERT INTO orders(id,goods_id,statu,price) VALUES('6','钞票','1',250);
INSERT INTO orders(id,goods_id,statu,price) VALUES('7','钞票','0',350);
INSERT INTO orders(id,goods_id,statu,price) VALUES('8','钞票','1',400);

统计方法：

1：

SELECT a.* FROM orders a ,(SELECT b.goods_id,MAX(b.price) price FROM orders b GROUP BY b.goods_id) c WHERE a.goods_id = c.goods_id AND a.`price` = c.price

2：

SELECT a.* FROM orders a WHERE NOT EXISTS(SELECT 1 FROM orders b WHERE  a.goods_id = b.goods_id AND a.price <b.price)