查找某个字段最大值的记录 SQL语句

查找某个字段最大值的 SQL语句 select table_name.* from table_name,(select max(price) as price,pid from table_name group by pid) as table_name_temp where table_name_temp.price=table_name.price and table_name_temp.pid=table_name.pid; --SQL code create table lk1 ( uid int, pid int, price int, `time` date )engine=myisam; insert into lk1 values (1, 1, 100, '2007-07-01'), (1, 2, 150, '2007-07-02 '), (2, 1, 110, '2007-07-03 '), (3, 1, 120, '2007-07-04 '), (4, 2, 180, '2007-07-04 '), (3, 2, 170, '2007-07-04 '), (6, 3, 130, '2007-07-04 '); select * from lk1 where price in (select max(price) from lk1 group by pid) group by pid; --结果1: query result(3 records) uid pid price time 3 1 120 2007-07-04 4 2 180 2007-07-04 6 3 130 2007-07-04 truncate table lk1; insert into lk1 values (1, 1, 200, '2007-07-01'), (1, 2, 200, '2007-07-02 '), (2, 1, 110, '2007-07-03 '), (3, 1, 120, '2007-07-04 '), (4, 2, 180, '2007-07-04 '), (3, 2, 170, '2007-07-04 '), (6, 3, 130, '2007-07-04 '); select * from lk1 where price in (select max(price) from lk1 group by pid) group by pid; --结果2: query result(3 records) uid pid price time 1 1 200 2007-07-01 1 2 200 2007-07-02 6 3 130 2007-07-04 根据mysql的手册上面找到一个查询,修改了一下发现也可以实现,但是并不 理解什么意思,不知道有没有人给解释一下 SQL codeSELECT * FROM lk1 l1 WHERE price = ( SELECT MAX( l2.price ) FROM lk1 l2 WHERE l1.`pid` = l2.`pid` ) ; --SQL code create table lk1 ( uid int, pid int, price int, `time` date )engine=myisam; insert into lk1 values (1, 1, 100, '2007-07-01'), (1, 2, 150, '2007-07-02 '), (2, 1, 110, '2007-07-03 '), (3, 1, 120, '2007-07-04 '), (4, 2, 180, '2007-07-04 '), (3, 2, 170, '2007-07-04 '), (6, 3, 130, '2007-07-04 '); select * from (select * from lk1 order by price desc) T group by pid; --结果1: query result(3 records) uid pid price time 3 1 120 2007-07-04 4 2 180 2007-07-04 6 3 130 2007-07-04 truncate table lk1; insert into lk1 values (1, 1, 200, '2007-07-01'), (1, 2, 200, '2007-07-02 '), (2, 1, 110, '2007-07-03 '), (3, 1, 120, '2007-07-04 '), (4, 2, 180, '2007-07-04 '), (3, 2, 170, '2007-07-04 '), (6, 3, 130, '2007-07-04 '); select * from (select * from lk1 order by price desc) T group by pid; --结果2: query result(3 records) uid pid price time 1 1 200 2007-07-01 1 2 200 2007-07-02 6 3 130 2007-07-04 insert into lk1 values (4, 3, 200, '2007-07-05 '), (5, 3, 210, '2007-07-05' ); select * from (select * from lk1 order by price desc) T group by pid; --结果3: query result(3 records) uid pid price time 1 1 200 2007-07-01 1 2 200 2007-07-02 5 3 210 2007-07-05