我遇到这样的一种需求:
A表字段:product_id(产品id),value(产品价格),datetime(时间)
每一种产品每天的价格都是会变化滴,我这边的需求就是取出前十条产品价格,要求时间是最新的十条,产品是不同的十种。
这可难倒我了,使用group by product_id吧,没有办法获得时间,按时间排序吧,无法得到不同的十种产品
求一条SQL语句,实现需求
A表字段:product_id(产品id),value(产品价格),datetime(时间)
每一种产品每天的价格都是会变化滴,我这边的需求就是取出前十条产品价格,要求时间是最新的十条,产品是不同的十种。
这可难倒我了,使用group by product_id吧,没有办法获得时间,按时间排序吧,无法得到不同的十种产品
求一条SQL语句,实现需求
from A a
where not exists (select 1 from A b where a.product_id=B.product_id and A.datetime<B.datetime)
order by A.datetime desc
limit 10;
-> `id` int not null auto_increment,
-> `product_id` int not null,
-> `value` decimal(11,2),
-> `datetime` datetime not null,
-> primary key(`id`)
-> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.08 sec)mysql> insert into product_value (product_id,value,datetime)
-> values
-> (1,11.2,'2012-05-25 11:00:05'),
-> (2,21.2,'2012-05-25 11:02:35'),
-> (3,10.3,'2012-05-25 11:15:38'),
-> (1,11.5,'2012-05-25 11:12:40'),
-> (2,19.8,'2012-05-25 12:32:00'),
-> (3,8.15,'2012-05-25 15:12:40'),
-> (4,68.15,'2012-05-25 16:12:40'),
-> (1,15,'2012-05-25 19:12:40'),
-> (3,8.16,'2012-05-25 20:22:42');
Query OK, 9 rows affected (0.03 sec)
Records: 9 Duplicates: 0 Warnings: 0mysql> select * from product_value;
+----+------------+-------+---------------------+
| id | product_id | value | datetime |
+----+------------+-------+---------------------+
| 1 | 1 | 11.20 | 2012-05-25 11:00:05 |
| 2 | 2 | 21.20 | 2012-05-25 11:02:35 |
| 3 | 3 | 10.30 | 2012-05-25 11:15:38 |
| 4 | 1 | 11.50 | 2012-05-25 11:12:40 |
| 5 | 2 | 19.80 | 2012-05-25 12:32:00 |
| 6 | 3 | 8.15 | 2012-05-25 15:12:40 |
| 7 | 4 | 68.15 | 2012-05-25 16:12:40 |
| 8 | 1 | 15.00 | 2012-05-25 19:12:40 |
| 9 | 3 | 8.16 | 2012-05-25 20:22:42 |
+----+------------+-------+---------------------+
9 rows in set (0.00 sec)mysql> select * from (select product_id,max(datetime) as datetime from product_value group by product_id desc limit 10) t order by datetime desc;
+------------+---------------------+
| product_id | datetime |
+------------+---------------------+
| 3 | 2012-05-25 20:22:42 |
| 1 | 2012-05-25 19:12:40 |
| 4 | 2012-05-25 16:12:40 |
| 2 | 2012-05-25 12:32:00 |
+------------+---------------------+
4 rows in set (0.00 sec)mysql>这个样子?
+----+------------+-------+---------------------+
| id | product_id | value | datetime |
+----+------------+-------+---------------------+
| 9 | 3 | 8.16 | 2012-05-25 20:22:42 |
| 8 | 1 | 15.00 | 2012-05-25 19:12:40 |
| 7 | 4 | 68.15 | 2012-05-25 16:12:40 |
| 5 | 2 | 19.80 | 2012-05-25 12:32:00 |
+----+------------+-------+---------------------+
from A t
where not exists (select 1 from A where product_id =t.product_id and datetime>t.datetime)
order by datetime desc limit 10
[征集]分组取最大N条记录方法征集,及散分....
where not exists(select 1 from A where product_id=a.product_id and datetime>a.datetime)
order by datatime desc
limit 10 ;