sql分组取最大记录方法-mysql教程

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

CREATE TABLE IF NOT EXISTS `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`install` int(10) unsigned NOT NULL,
`day` int(10) unsigned NOT NULL,
`aid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

INSERT INTO `test` (`id`, `install`, `day`, `aid`) VALUES
(1, 'www.111cn.net', 20120308, 1),
(2, 2321, 20120309, 2),
(3, 1236, 20120310, 3),
(5, 'www.hzhuti.com', 20120309, 1),
(6, 2312, 20120310, 1),
(7, 1432, 20120311, 1),
(8, 2421, 20120308, 2),
(9, 4245, 20120311, 2),
(10, 'www.111cn.net', 20120310, 2),
(11, 412, 20120308, 3);

SELECT A.* FROM test A,
(SELECT aid, MAX(day) max_day FROM test GROUP BY aid) B
WHERE A.aid = B.aid AND A.day = B.max_day
ORDER BY a.install DESC

a b c d
1 0 c1 d1
1 1 c2 d2
1 3 c3 d3

4 0 c4 d4

5 1 c5 d5
5 2 c6 d6

6 1 c7 d7
6 4 c8 d8

* from table1 where b in (select max(b) from table1 group by a) ;

select * from table1 a where b=(select max(b) from table1 where a=a.a)