MYSQL，在单行中返回 JOIN 中的第一行和最后一行

Question

我有两个表，一个是项目目录，另一个包含这些项目的历史记录。

我想返回与项目表连接的时间段内最早和最新历史记录行的值，使每个项目一行包含这两个提到的值。

表结构如下:

items
-----
item_id, item_title

hist
-------
hist_id, hist_item_id (corresponds to items.item_id), hist_value, hist_time (stores Unix timestamp)

我已经用一些示例数据创建了一个 SQL fiddle 来帮助你，http://www.sqlfiddle.com/#!2/c78363/3

作为最终结果，我希望看到像这样返回的行，使用我的 fiddle 中的示例数据将在 1420291000 和 1420294000 之间的时间段:

items.item_id   items.item_title   history_earliest   history_latest
1               ABC                1                  5
2               XYZ                1                  9

目前，我正在对项目运行查询以返回所有项目，然后在 PHP 中迭代这些结果，然后运行两个查询给我最早的值，然后另一个查询给我最新的值，所以我确信效率很高可以节省开支，但我不知道从哪里开始!

提前致谢

Answer 1

您可以使用 GROUP_CONCAT 获取您时间范围内的所有值，当然您只需要第一个和最后一个值。您可以在代码中执行此操作，也可以使用 SUBSTRING_INDEX 执行此操作。

http://www.sqlfiddle.com/#!2/c78363/41

SELECT
  items.item_id,
  items.item_title,
  SUBSTRING_INDEX(GROUP_CONCAT(hist.hist_value ORDER BY hist.hist_id ASC), ',', 1) AS earliest,
  SUBSTRING_INDEX(GROUP_CONCAT(hist.hist_value ORDER BY hist.hist_id DESC), ',', 1) AS latest
FROM hist
INNER JOIN items ON hist.hist_item_id = items.item_id
WHERE hist.hist_time BETWEEN 1420291000 AND 1420294000
GROUP BY hist.hist_item_id