代码之家  ›  专栏  ›  技术社区  ›  random_user_name

在mysql子查询中按日期获取第一条记录和最后一条记录的值

  •  0
  • random_user_name  · 技术社区  · 7 年前

    我知道对于一些MySQL pro来说,这相当简单。我进一步意识到答案可能是 figured out from other answers 然而,我花了一些时间尝试构建这个查询,但我似乎不知道如何将这些解决方案应用到我的情况中。

    看起来不一样 比其他想要字段的“最小值和最大值”的人,但我需要 另一个字段 基于日期字段的“最小值和最大值”。

    给定以下结构-一个“用户”表和一个“条目”表:

    数据示例(对于“条目”表):

    id | user_id |     date     | value  
    ---+---------+--------------+-------
     1         1     2018-02-01     125
     2         5     2018-01-15     220
     3         1     2017-12-31     131
     4         4     2018-01-01      77
     3         1     2017-12-15     133
    

    我想知道 首次录入(按日期) ,的值 最后一个条目 (按日期)和用户id。

    结果应为:

    user_id | first_date | first_value |  last_date | last_value
    --------+------------+-------------+------------+-----------
          1   2017-12-15           133   2018-02-01          125
          4   2018-01-01            77   2018-01-01          133
          5   2018-01-15           220   2018-01-15          220
    

    当我想要的时候 最好的 解决方案,我一直致力于将以下查询组合在一起:

    SELECT user_id, l.date AS last_date, l.value AS last_value, f.date AS first_date, f.value AS first_value  
        FROM user AS u
        LEFT JOIN (SELECT user_id, date, value FROM entries ORDER BY date ASC LIMIT 1) AS f ON f.user_id = u.user_id
        LEFT JOIN (SELECT user_id, date, value FROM entries ORDER BY date DESC LIMIT 1) AS l ON l.user_id = u.user_id
    

    注意:这不起作用。如果我想要某人的“第一个条目”,我会编写一个查询 SELECT user_id, date, value FROM entries ORDER BY date ASC LIMIT 1 -然而,在子查询中使用它并没有达到预期的效果。

    我也试过一些 GROUP BY 查询,也没有成功。

    SQL FIDLE: http://sqlfiddle.com/#!9/71599

    1 回复  |  直到 7 年前
        1
  •  1
  •   fubar    7 年前

    下面的查询提供了预期的结果,但没有使用 LEFT JOIN . 所以 NULL 排除值。

    SELECT 
        u.id AS user_id,
        e1.date AS first_date,
        e1.value AS first_value,
        e2.date AS last_date,
        e2.value AS last_value
    FROM 
        users u, 
        (SELECT * FROM entries e ORDER BY date ASC) e1,
        (SELECT * FROM entries e ORDER BY date DESC) e2
    WHERE
        e1.user_id = u.id
    AND 
        e2.user_id = u.id
    GROUP BY 
        u.id
    

    这是一把小提琴- http://sqlfiddle.com/#!9/71599/8

    此外,值得注意的是 LIMIT 在您的尝试中,将所有联接结果的结果限制为1,而不是每个联接结果。不管怎样 左连接 不起作用。如果有人知道原因,我很想了解。

    编辑: 这是另一次尝试,这次利用 MIN() MAX() 而不是 ORDER BY . 很遗憾,您需要加入 entries 表多次执行此操作。

    SELECT 
        u.id AS user_id,
        e1.date AS first_date,
        e1.value AS first_value,
        e2.date AS last_date,
        e2.value AS last_value
    FROM users u
    INNER JOIN entries e1 ON (u.id = e1.user_id)
    INNER JOIN entries e2 ON (u.id = e2.user_id)
    INNER JOIN (
        SELECT user_id, MIN(date) AS date
        FROM entries
        GROUP BY user_id
    ) e3 ON (e1.user_id = e3.user_id AND e1.date = e3.date)
    INNER JOIN (
        SELECT user_id, MAX(date) AS date
        FROM entries
        GROUP BY user_id
    ) e4 ON (e2.user_id = e4.user_id AND e2.date = e4.date)
    GROUP BY u.id
    

    另一把小提琴: http://sqlfiddle.com/#!9/71599/18