在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该怎么写呢?求高人指点。
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该怎么写呢?求高人指点。
解决方案 »
- MySQL Error:2013
- mysql 触发器 希望在A表insert之后,触发B表的insert操作
- 白给分的题哦,来抢啊
- 使用mysql-front添加外键 失败(如图)
- mysql错误“Got error 134 from storage engine”
- MYSQL中如何查询同一列多个字段之和,并返回结果中最大的top5的ID呢
- 谁有mysql的安装软件啊
- 卸载了MYSQL,但是MYSQL文件夹删除不了啊,因为进程里还有一个mysqld-nt.exe结束不了,这么办啊?
- 求一段sql,谢谢
- windows下mysql 自动安装及配置
- 数据库多条件查询
- 求解释啊欲哭无泪看不懂 mysql实现分组后组内排序
inner join a1 b
on a.`a1_id`=b.idWHERE NOT EXISTS(SELECT 1 FROM a2 WHERE a.`a1_id`=`a1_id` AND a.`record_time`<`record_time`)
这个应该可以
FROM a1
LFTT JOIN a2
ON a1.id=a2.id
group by a1.id,a1.name
HAVING MAX(a2.record_time)=3