MySQL-针对一定范围的列选择不同的值 [英] MySQL - select distinct values against a range of columns
问题描述
下表将存储一段时间内各种货币之间的汇率:
The following table will store exchange rates between various currencies over time:
CREATE TABLE `currency_exchange` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`currency_from` int(11) DEFAULT NULL,
`currency_to` int(11) DEFAULT NULL,
`rate_in` decimal(12,4) DEFAULT NULL,
`rate_out` decimal(12,4) DEFAULT NULL,
`exchange_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
我将如何查询它以获取最新汇率列表?
How would I query it to fetch a list the most recent exchange rates?
从本质上讲,这将标识合并的 currency_from 和 currency_to 列为不同的汇率,然后找到具有最新日期的列.
It'd essentially be identifying the combined currency_from and currency_to columns as a distinct exchange rate, then finding the one with the most recent date.
例如,假设我的数据为:
For example, let's say I've got the data as:
INSERT INTO currency_exchange (id, currency_from, currency_to, rate_in, rate_out, exchange_date) VALUES
(1, 1, 2, 0.234, 1.789, '2012-07-23 09:24:34'),
(2, 2, 1, 0.234, 1.789, '2012-07-24 09:24:34'),
(3, 2, 1, 0.234, 1.789, '2012-07-24 09:24:35'),
(4, 1, 3, 0.234, 1.789, '2012-07-24 09:24:34');
我希望它选择行ID:
- 1-作为货币1和2之间的最新汇率
- 3-作为货币2和1之间的最新汇率
- 4-作为货币1和3之间的最新汇率
推荐答案
以下查询应该有效:
SELECT ce.*
FROM currency_exhcnage ce
LEFT JOIN currency_exchange newer
ON (newer.currency_from = ce.currency_from
AND newer.currency_to = ce.currency_to
AND newer.exchange_date > ce.exchange_date)
WHERE newer.id IS NULL
进行自我 LEFT JOIN
的技巧是避免求助于子查询,如果您有大量数据集,子查询可能会非常昂贵.本质上是在寻找不存在较新"记录的记录.
The trick of doing a self-LEFT JOIN
is to avoid resorting to a subquery that may be very expensive if you have large datasets. It's essentially looking for records where no "newer" record exists.
或者,您可以选择一个更简单的方法(尽管它可能(或可能不会,如注释中所述)比较慢):
Alternatively, you could go for a simpler (although it may (or may not, as noted in comments) be slower):
SELECT *
FROM currency_exchange ce
NATURAL JOIN (
SELECT currency_from, currency_to, MAX(exchange_date) AS exchange_date
FROM currency_exchange
GROUP BY currency_from, currency_to
) AS most_recent
这篇关于MySQL-针对一定范围的列选择不同的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!