mysql - 如何从日期到列中每个给定行值中获取最后一行？

Question

这个问题已经有答案了:

奥 git _a (33 个回答)

已关闭 4 年前。

我有下表。

+--------------------+--------------+-------+
Date                 | SymbolNumber | Value
+--------------------+--------------+-------+
 2018-08-31 15:00:00 | 123          | data
 2018-09-31 15:00:00 | 456          | data
 2018-09-31 15:00:00 | 123          | data
 2018-09-31 15:00:00 | 555          | data
 2018-10-31 15:00:00 | 555          | data
 2018-10-31 15:00:00 | 231          | data
 2018-10-31 15:00:00 | 123          | data
 2018-11-31 15:00:00 | 123          | data
 2018-11-31 15:00:00 | 555          | data
 2018-12-31 15:00:00 | 123          | data
 2018-12-31 15:00:00 | 555          | data

我需要一个可以选择查询中指定的每个 SymbolNumber 的最后一行的查询。

SELECT
    *
FROM
    MyTable
WHERE
    symbolNumber IN (123, 555)
AND
    **lastOfRow ordered by latest-date**

预期结果:

 2018-12-31 15:00:00 | 123 | data
 2018-12-31 15:00:00 | 555 | data

我该怎么做？

Answer 1

首先，您需要一个查询来获取每个 symbolNumber 的最新日期。其次，您可以内部联接到该表(使用日期)来获取其余的列。像这样:

SELECT
    t.*
FROM
   <table_name> AS t
INNER JOIN
    (SELECT
        symbolNumber,
        MAX(date) AS maxDate
    FROM
        <table_name>
    GROUP BY
       symbolNumber) AS latest_date ON latest_date.symbolNumber = t.symbolNumber AND latest_date.maxDate = t.date

上一个查询将获取表中每个现有 symbolNumber 的最新数据。如果您想限制为symbolNumbers:123和555，您将需要进行下一步修改:

SELECT
    t.*
FROM
   <table_name> AS t
INNER JOIN
    (SELECT
        symbolNumber,
        MAX(date) AS maxDate
    FROM
        <table_name>
    WHERE
        symbolNumber IN (123, 555)
    GROUP BY
       symbolNumber) AS latest_date ON latest_date.symbolNumber = t.symbolNumber AND latest_date.maxDate = t.date