关于group by语句mysql解决方案-MySQL教程-爱易网页

关于group by语句mysql解决方案

日期：2014-05-16　浏览次数：20852 次

关于group by语句mysql
我遇到这样的一种需求：
A表字段：product_id(产品id)，value（产品价格），datetime（时间）
每一种产品每天的价格都是会变化滴，我这边的需求就是取出前十条产品价格，要求时间是最新的十条，产品是不同的十种。
这可难倒我了，使用group by product_id吧，没有办法获得时间，按时间排序吧，无法得到不同的十种产品
求一条SQL语句，实现需求

------解决方案--------------------
按时间牌序然后distinct步行么

------解决方案--------------------
select *
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;
------解决方案--------------------

SQL code

mysql> create table product_value(
    ->  `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: 0

mysql> 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>

------解决方案--------------------
晕，忘记价格了

------解决方案--------------------
SQL codeselect * from product_value where id in(select max(id) as id from product_value group by product_id) order by datetime desc limit 10;
+----+------------+-------+---------------------+
| 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 |
+----+------------+-------+---------------------+

------解决方案--------------------
SQL codeselect *
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

------解决方案--------------------
参考下贴中的多种方法

http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集，及散分....

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

关于group by语句mysql解决方案

相关资料更多>

推荐阅读更多>