MySQL从特定的相关记录中获取所有数据

Question

我正在寻找一种为MySQL表中的每个记录输出选定的相关记录的方法。我会进一步解释...

我有2个表格货币和 exchange_rates 。这些表由 currency_code 字段连接，每个货币记录具有多个相关的汇率记录，每个汇率记录代表一个不同的日期。因此，货币与汇率之间存在1:1的关系。

我想从exchange_rates表中检索每种货币的完整记录，但是能够定义关于选择哪个相关记录的特定条件。不仅是每种货币的最新exchange_rate，还可能是具有exchange_rates字段的每种货币的最新criteria_x=NULL记录。

很遗憾您不能在派生表中使用LIMIT，否则类似的方法将是一种简洁易读的解决方案...

SELECT `currencies`.`currency_code`, `currencies`.`country`, `exchange_rates`.`id`,
       FROM_UNIXTIME(`exchange_rates`.`datestamp`), `rate` 
FROM `currencies` 
INNER JOIN (
SELECT `id`, `currency_code`, `invoice_id`, `datestamp`, `rate` 
FROM `exchange_rates` 
WHERE `criteria_x`=NULL AND `criteria_y` LIKE 'A' 
ORDER BY `datestamp` DESC
LIMIT 0, 1
) AS `exchange_rates` ON `currencies`.`currency_code`=`exchange_rates`.`currency_code`
ORDER BY `currencies`.`country`

LIMIT子句应用于父查询，而不应用于派生表。

这是我发现这样做的唯一方法...

SELECT `currencies`.`currency_code`, `currencies`.`country`, 
FROM_UNIXTIME( SUBSTRING_INDEX( SUBSTRING_INDEX(`exchange_rates`.`concat`, '-', 1), '-', -1)) AS `datestamp`,
SUBSTRING_INDEX( SUBSTRING_INDEX(`exchange_rates`.`concat`, '-', 2), '-', -1) AS `id`, 
SUBSTRING_INDEX( SUBSTRING_INDEX(`exchange_rates`.`concat`, '-', 3), '-', -1) AS `rate` 
FROM `currencies`
INNER JOIN (
SELECT `currency_code`, MAX(CONCAT_WS('-', `datestamp`, `id`, `rate`)) AS `concat`
FROM `exchange_rates` 
WHERE `criteria_x`=NULL AND `criteria_y` LIKE 'A' 
GROUP BY `exchange_rates`.`currency_code`
) AS `exchange_rates` ON `currencies`.`currency_code`=`exchange_rates`.`currency_code`
ORDER BY `currencies`.`country`

因此，将一堆字段连接在一起并在其上运行 MAX()以获得我在该组中的排序顺序，然后使用 SUBSTRING_INDEX()在父查询中解析这些字段。问题是，仅当我可以在串联字段上使用 MIN()或 MAX()时，此方法才有效。如果我想对字符串排序或按多个条件排序但限制为单个记录，那将不是理想的选择。

同样，这也使我非常痛苦，不得不使用可怕的字符串操作来从关系数据库中获取我想要的数据-必须有更好的方法!

有人对更好的方法有什么建议吗？

Answer 1

在尝试提供答案之前，有一些一般性问题需要简短讨论。

您的第一个查询是:

SELECT `currencies`.`currency_code`, `currencies`.`country`, `exchange_rates`.`id`,
       FROM_UNIXTIME(`exchange_rates`.`datestamp`), `rate` 
FROM `currencies` 
INNER JOIN (
SELECT `id`, `currency_code`, `invoice_id`, `datestamp`, `rate` 
FROM `exchange_rates` 
WHERE `criteria_x`=NULL AND `criteria_y` LIKE 'A' 
ORDER BY `datestamp` DESC
LIMIT 0, 1
) AS `exchange_rates` ON `currencies`.`currency_code`=`exchange_rates`.`currency_code`
ORDER BY `currencies`.`country`

我认为您不需要使用多少back-quotes。它们不是完全错误，但是我不会在答案中键入它们。

SQL标准未批准criteria_x = NULL表示法；应该写为criteria_x IS NULL。 MySQL可能允许它；只要您知道它是非标准的，就可以使用。

如果条件LIKE 'A'不包含任何元字符(标准SQL中的%或_)，则它是不明智的。使用简单的相等性会更好:= 'A'。

您的问题是:

I want to retrieve a full record from the exchange_rates table for each currency but with the ability to define specific criteria as to which related record to select. Not just the most recent exchange rate for each currency, but maybe the most recent exchange rate for each currency that has the field criteria_x IS NULL.

因此，您想要为每种货币选择符合要求的其他条件的最新汇率记录。我们可以假设汇率表中对 currency_code和 datestamp的组合存在唯一约束；这意味着将始终最多有一个匹配行。您没有指定在没有匹配行的情况下应显示的内容。当然，内部联接不会简单地列出该货币。

对于SQL查询，我通常分步构建和测试整个查询，向已知的可以有效工作并产生正确输出的先前开发的查询添加额外的 Material 。如果它很简单和/或我收集了太多的自负，我将首先尝试一个复杂的查询，但是当(nemesis)它不起作用时，我将返回到构建和测试过程。可以将其视为测试驱动(查询)开发。

阶段1:符合指定条件的汇率记录

SELECT id, currency_code, invoice_id, datestamp, rate 
  FROM exchange_rates 
 WHERE criteria_x IS NULL AND criteria_y = 'A' 
 ORDER BY currency_code, datestamp DESC

阶段2:符合指定条件的每种货币的最新汇率时间

SELECT currency_code, MAX(datestamp) 
  FROM exchange_rates 
 WHERE criteria_x IS NULL AND criteria_y = 'A' 
 GROUP BY currency_code

第3阶段:与指定条件匹配的每种货币的最新汇率时间的汇率记录

SELECT x.id, x.currency_code, x.invoice_id, x.datestamp, x.rate 
  FROM exchange_rates AS x
  JOIN (SELECT currency_code, MAX(datestamp) AS datestamp
          FROM exchange_rates 
         WHERE criteria_x IS NULL AND criteria_y = 'A' 
         GROUP BY currency_code
       ) AS m
    ON x.currency_code = m.currency_code AND x.datestamp = m.datestamp

阶段4:符合指定条件的每种货币的最新汇率时间的货币信息和汇率记录

这需要将currencys表与上一个查询的输出结合起来:

SELECT c.currency_code, c.country, r.id,
       FROM_UNIXTIME(r.datestamp), r.rate
  FROM currencies AS c 
  JOIN (SELECT x.id, x.currency_code, x.invoice_id, x.datestamp, x.rate 
          FROM exchange_rates AS x
          JOIN (SELECT currency_code, MAX(datestamp) AS datestamp
                  FROM exchange_rates 
                 WHERE criteria_x IS NULL AND criteria_y = 'A' 
                 GROUP BY currency_code
               ) AS m
            ON x.currency_code = m.currency_code AND x.datestamp = m.datestamp
       ) AS r
    ON c.currency_code = r.currency_code
 ORDER BY c.country

除了Oracle只允许使用' ) r'代替' ) AS r'作为表别名和使用 FROM_UNIXTIME()之外，我相信您应该提到的几乎所有SQL DBMS的当前版本都可以正常使用。

由于发票ID不会在最终查询中返回，因此我们可以从中间查询的选择列表中将其删除。好的优化程序可能会自动执行此操作。

如果即使没有符合条件的汇率也要查看货币信息，则需要将最外面的查询中的JOIN更改为LEFT JOIN(又名LEFT OUTER JOIN)。如果您只想查看货币的子集，则可以在最后一个(最外面的)查询阶段应用该过滤器，或者(如果该过滤器基于汇率表中的可用信息，例如货币代码)在以下位置应用该过滤器最里面的子查询(最有效)或中间的子查询(除非优化器意识到它可以将过滤器向下推到最里面的子查询，否则效率不高)。

正确性通常是主要标准；性能是次要标准。但是，问题中提到了性能。第一条规则是衡量此处显示的“简单”查询。仅在证明速度太慢的情况下，您才需要进一步担心。当您确实需要担心时，可以检查查询计划以查看是否缺少例如重要索引。仅当查询仍然不够快时，您才开始尝试使用其他技巧。这些技巧往往是特定于特定DBMS的。例如，可能存在优化程序提示，可用于使DBMS以不同的方式处理查询。