DROP TABLE IF EXISTS `aaa`;
CREATE TABLE `aaa` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`akey` bigint(20) NOT NULL,
`avalue` bigint(20) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录日期',
PRIMARY KEY(`id`)
)ENGINE=InnoDB CHARSET=utf8;
INSERT INTO aaa(akey,avalue,time)VALUES
(1,2,'2010-10-09 15:58:20'),
(1,2,'2010-10-09 15:58:21'),
(1,2,'2010-10-09 15:58:22'),
(2,2,'2010-10-09 15:58:20'),
(2,2,'2010-10-09 15:58:21'),
(2,1,'2010-10-09 15:58:28');
我想根据akey和avalue两个条件进行判断查询,只要满足他们两个值都相等记录的最新一条就行,
例如
(1,2,'2010-10-09 15:58:20'),
(1,2,'2010-10-09 15:58:21'),
(1,2,'2010-10-09 15:58:22'),
这三条记录都一样的,key与value都相等,我要取最新一条记录,也就是最后一条,mysql语句怎么写?
CREATE TABLE `aaa` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`akey` bigint(20) NOT NULL,
`avalue` bigint(20) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录日期',
PRIMARY KEY(`id`)
)ENGINE=InnoDB CHARSET=utf8;
INSERT INTO aaa(akey,avalue,time)VALUES
(1,2,'2010-10-09 15:58:20'),
(1,2,'2010-10-09 15:58:21'),
(1,2,'2010-10-09 15:58:22'),
(2,2,'2010-10-09 15:58:20'),
(2,2,'2010-10-09 15:58:21'),
(2,1,'2010-10-09 15:58:28');
我想根据akey和avalue两个条件进行判断查询,只要满足他们两个值都相等记录的最新一条就行,
例如
(1,2,'2010-10-09 15:58:20'),
(1,2,'2010-10-09 15:58:21'),
(1,2,'2010-10-09 15:58:22'),
这三条记录都一样的,key与value都相等,我要取最新一条记录,也就是最后一条,mysql语句怎么写?
set time=b.time1
where a.akey=b.akey and a.avalue=b.avalue;
select * from aaa t where not exists(select 1 from aaa where aaa.akey=t.avalue and aaa.time>t.time)
这个语句应该不是LZ 所需要的。 我想根据akey和avalue两个条件进行判断查询,只要满足他们两个值都相等记录的最新一条就行,
例如
(1,2,'2010-10-09 15:58:20'),
(1,2,'2010-10-09 15:58:21'),
(1,2,'2010-10-09 15:58:22'),
这三条记录都一样的,key与value都相等,我要取最新一条记录,也就是最后一条,mysql语句怎么写?
[征集]分组取最大N条记录方法征集,及散分....
where not Exists (select 1 from aaa where akey=t.akey and avalue=t.avalue and `time`>t.time);mysql> select * from aaa t
-> where not Exists (select 1 from aaa where akey=t.akey and avalue=t.avalue
and `time`>t.time);
+----+------+--------+---------------------+
| id | akey | avalue | time |
+----+------+--------+---------------------+
| 3 | 1 | 2 | 2010-10-09 15:58:22 |
| 5 | 2 | 2 | 2010-10-09 15:58:21 |
| 6 | 2 | 1 | 2010-10-09 15:58:28 |
+----+------+--------+---------------------+
3 rows in set (0.05 sec)mysql>select *
from (
select * from aaa Order by akey,avalue,time desc
) t
group By akey,avaluemysql> select *
-> from (
-> select * from aaa order by akey,avalue,time desc
-> ) t
-> group by akey,avalue;
+----+------+--------+---------------------+
| id | akey | avalue | time |
+----+------+--------+---------------------+
| 3 | 1 | 2 | 2010-10-09 15:58:22 |
| 6 | 2 | 1 | 2010-10-09 15:58:28 |
| 5 | 2 | 2 | 2010-10-09 15:58:21 |
+----+------+--------+---------------------+
3 rows in set (0.00 sec)mysql>
update aaa a,(select id,akey,avalue,time as time1 from aaa where id in (select max(id) from aaa group by akey,avalue)) b
set time=b.time1
where a.akey=b.akey and a.avalue=b.avalue;
想问ACMAIN_CHM: 第一种解法结果在我这里执行结果不对喔, 为什么呢? 第二种解法结果正确。
mysql> select * from aaa t
-> where not Exists (select 1 from aaa where akey=t.akey and avalue=t.avalue
and 'time'=t.time);
+----+------+--------+---------------------+
| id | akey | avalue | time |
+----+------+--------+---------------------+
| 1 | 1 | 2 | 2010-10-09 15:58:22 |
| 2 | 1 | 2 | 2010-10-09 15:58:22 |
| 3 | 1 | 2 | 2010-10-09 15:58:22 |
| 4 | 2 | 2 | 2010-10-09 15:58:21 |
| 5 | 2 | 2 | 2010-10-09 15:58:21 |
| 6 | 2 | 1 | 2010-10-09 15:58:28 |
+----+------+--------+---------------------+
6 rows in set, 1 warning (0.00 sec)
如果你的MYSQL的SQL MODE设置成标准的,则这个语句会报错。当然这是我猜的。 建议提类似这种问题的时候把错误信息同时贴出以供别人分析而不是让别人猜。问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
语句执行并没有报错, 但是结果并不是像你得到的一样, 我已经贴出在12楼。 期望的结果应该是只有3行, 但在我这里执行结果有6行, 也就是说并没有得出想要的结果。
另, 没有注意到也不知道如何设置 SQL MODE, 按照安装手册安装好 MYSQL 之后就没有进行过特别的什么设置。
从楼主的条件中, 没有说明 ID 大的记录时间就一定是最晚的, 因此用ID 去判断不一定能得出最准确的结果。 当然楼主的例子中正好是 ID 大, 时间就晚, 只能认为是特例。
哦,看错了。 你的是第一种EXISTS有问题。这个是标准SQL语句。 GROUP BY的是有问题的。select * from aaa ;你的结果是什么?
mysql> select * from aaa t;
+----+------+--------+---------------------+
| id | akey | avalue | time |
+----+------+--------+---------------------+
| 1 | 1 | 2 | 2010-10-09 15:58:20 |
| 2 | 1 | 2 | 2010-10-09 15:58:21 |
| 3 | 1 | 2 | 2010-10-09 15:58:22 |
| 4 | 2 | 2 | 2010-10-09 15:58:20 |
| 5 | 2 | 2 | 2010-10-09 15:58:21 |
| 6 | 2 | 1 | 2010-10-09 15:58:28 |
+----+------+--------+---------------------+
6 rows in set (0.00 sec)mysql> select * from aaa t
-> where not Exists (select 1 from aaa where akey=t.akey and avalue=t.avalue
and time>t.time);
+----+------+--------+---------------------+
| id | akey | avalue | time |
+----+------+--------+---------------------+
| 3 | 1 | 2 | 2010-10-09 15:58:22 |
| 5 | 2 | 2 | 2010-10-09 15:58:21 |
| 6 | 2 | 1 | 2010-10-09 15:58:28 |
+----+------+--------+---------------------+
3 rows in set (0.00 sec)mysql> select * from aaa t
-> where not Exists (select 1 from aaa where akey=t.akey and avalue=t.avalue
and 'time'>t.time);
Empty set, 1 warning (0.00 sec)Warning (Code 1292): Incorrect datetime value: 'time' for column 'time' at row 1mysql>
那这个就是正确的了