MySQL中，求一条SQL语句

[rockets311]

在MySQL中：

CREATE TABLE `a1` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `a1` VALUES (1,'a');
INSERT INTO `a1` VALUES (2,'b');
INSERT INTO `a1` VALUES (3,'c');

CREATE TABLE `a2` (
  `id` int(11) NOT NULL auto_increment,
  `a1_id` int(11) default NULL,
  `record_time` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `a2` VALUES (1,1,'2011-12-30 00:00:00');
INSERT INTO `a2` VALUES (2,1,'2011-11-30 00:00:00');
INSERT INTO `a2` VALUES (3,1,'2012-02-10 00:00:00');
INSERT INTO `a2` VALUES (4,2,'2012-10-30 00:00:00');
INSERT INTO `a2` VALUES (5,2,'2012-12-10 00:00:00');
INSERT INTO `a2` VALUES (6,3,'2012-10-30 00:00:00');

a1表：
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
a2表：其中a1_id是表a1的id，record_time是添加时间
+----+-------+---------------------+
| id | a1_id | record_time |
+----+-------+---------------------+
| 1 | 1 | 2011-12-30 00:00:00 |
| 2 | 1 | 2011-11-30 00:00:00 |
| 3 | 1 | 2012-02-10 00:00:00 |
| 4 | 2 | 2012-10-30 00:00:00 |
| 5 | 2 | 2012-12-10 00:00:00 |
| 6 | 3 | 2012-10-30 00:00:00 |
+----+-------+---------------------+
现要求查询结果为：即record_time是最新的。
+----+------+---------------------+
| id | name | record_time |
+----+------+---------------------+
| 1 | a | 2012-02-10 00:00:00 |
| 2 | b | 2012-12-10 00:00:00 |
| 3 | c | 2012-10-30 00:00:00 |
+----+------+---------------------+
这条SQL该怎么写呢？求高人指点。

[wwwwb]

SELECT * FROM a2 a
inner join a1 b
on a.`a1_id`=b.id



WHERE NOT EXISTS(SELECT 1 FROM a2 WHERE a.`a1_id`=`a1_id` AND a.`record_time`<`record_time`)

[看看看灰机]

select a1.id ,a1.name,max(record_time) from a1 LEFT join a2 on a1.id=a2.a1_id group by a1.id
这个应该可以

[Rotel-刘志东]

SELECT a1.id,a1.name,MAX(a2.record_time)
FROM a1
LFTT JOIN a2
ON a1.id=a2.id
group by a1.id,a1.name
HAVING MAX(a2.record_time)=3