mysql - 按列分组，返回最近和最早的字段

Question

我有一张 table :

ITEM    NOTE        RECORD_DATE             USER
Apple   Blah        2016-12-30 11:22:33     mcmurphy510
Orange  Somenote    2016-12-31 11:22:33     user2
Apple   BlahBlah    2017-01-01 11:22:33     user2

我正在尝试创建一个查询来显示项目和最近的注释。我还需要从 DATE 和 USER 列中导出四个字段:

CREATED:应该是给定 ITEM

的 最早行的 RECORD_DATE 时间戳

CREATED_BY:应该是给定 ITEM 的 最早 行的 USER

UPDATED:应该是给定 ITEM

的 最新行的 RECORD_DATE 时间戳

UPDATED_BY:应该是给定 ITEM 的 最新 行的 USER

我已经设法获得一个或另一个(最早或最新)，但不是两个，使用以下查询:

SELECT
    i.item,
    i.note,
    i.record_date AS created,
    i.user AS created_by
FROM
    item i
    INNER JOIN (
        SELECT
            item,
            MAX(record_date) as record_date
        FROM item
        GROUP BY item
    ) i2
        ON (
            i.item = i2.item AND
            i.record_date = i2.record_date
        )

使用这个，我可以在返回最早和最近的 RECORD_DATE 之间切换子查询。

我的问题是如何创建一个可以在同一行中同时显示最早和最新结果的单查询？

期望的结果:

ITEM    NOTE        CREATED                 CREATED_BY      UPDATED                 UPDATED_BY
Apple   BlahBlah    2016-12-30 11:22:33     mcmurphy510     2017-01-01 11:22:33     user2
Orange  Somenote    2016-12-31 11:22:33     user2           NULL                    NULL

我这里有个 fiddle : http://sqlfiddle.com/#!9/cd724a/3

Answer 1

您可以找到每个项目的最早和最晚日期，然后像这样连接到主表:

select
    i1.item,
    i2.note,
    i1.record_date created_date,
    i1.user create_user,
    case when i1.record_date <> i2.record_date then i2.record_date end update_date,
    case when i1.record_date <> i2.record_date then i2.user end update_user
from item i1
inner join item i2
on i1.item = i2.item
inner join (
select item, min(record_date) mn, max(record_date) mx
from item
group by item
) i3 on i1.item = i3.item
and i1.record_date = i3.mn
and i2.record_date = i3.mx;

SQLFiddle