我有一个价格表price_table,表中有多个商品,每种商品在不同时期可能有不同价格。现在我要查询每个产品的最新价格怎么写sql语句?
下面语句选出来的不是最新的:
SELECT * FROM price_table GROUP BY commodity_id order by time desc;
下面语句选出来的不是最新的:
SELECT * FROM price_table GROUP BY commodity_id order by time desc;
+--------------+---------------------+-------+
| commodity_id | time | price |
+--------------+---------------------+-------+
| 1 | 2011-01-16 11:55:07 | 102 |
| 2 | 2011-01-16 11:55:24 | 103 |
+--------------+---------------------+-------+
2 rows in set (0.00 sec)
mysql> select * from price_table;
+--------------+---------------------+-------+
| commodity_id | time | price |
+--------------+---------------------+-------+
| 1 | 2011-01-16 11:54:56 | 100 |
| 1 | 2011-01-16 11:55:07 | 102 |
| 2 | 2011-01-16 11:55:19 | 99 |
| 2 | 2011-01-16 11:55:24 | 103 |
+--------------+---------------------+-------+
4 rows in set (0.00 sec)
SELECT * FROM price_table order by time desc
) a
GROUP BY commodity_id 先order by 试下
[征集]分组取最大N条记录方法征集,及散分....