MYSQL - 返回 GROUP BY 之前的最新记录

Question

我已经阅读了很多关于与这个非常相似的问题的问题和答案，主要问题是他们使用 LIMIT 作为返回最新记录的解决方案，该记录将无法使用这个查询是因为我需要返回多个结果。

逻辑如下:

• 每条记录都有一个effective_date，即记录值生效的时间。
• 仅提取 effective_date 是今天或已经过去的记录
• 在我撤回的记录中，根据该记录的“date_entered”给我最新的记录

到目前为止，我已经能够使用此查询完成除最后一点逻辑之外的所有操作。我不能在 GROUP BY 之后使用 ORDER BY，因为结果已经分组所以我不能使用 MAX()日期。我需要在 WHERE 语句中执行逻辑。

SELECT
    aos_products.`name`,
    cac_customize_agent_comp_cstm.commission_percentage_c,
    cac_customize_agent_comp.date_entered
FROM
    wn_writing_number
LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number.id = wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida
LEFT OUTER JOIN cac_customize_agent_comp_cstm ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp_cstm.id_c
LEFT OUTER JOIN cac_customize_agent_comp ON cac_customize_agent_comp_cstm.id_c = cac_customize_agent_comp.id
LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
LEFT OUTER JOIN aos_products ON aos_products_cac_customize_agent_comp_1_c.aos_products_cac_customize_agent_comp_1aos_products_ida = aos_products.id
WHERE
    wn_writing_number.id = 'b556d816-f2e6-b78f-c776-576c0542585c'
AND wn_writing_number_cac_customize_agent_comp_1_c.deleted = '0'
AND cac_customize_agent_comp_cstm.effective_date_c <= CURDATE()
GROUP BY aos_products.`name`

没有 GROUP BY，它返回:

     (varchar)     (varchar)                 (datetime)
    +---------+-------------------------+----------------------+
    | name    | commission_percentage_c |     date_entered     |
    +---------+-------------------------+----------------------+
    | Val 1   | 15.25                   | 2016-07-06 23:57:28  |
    +---------+-------------------------+----------------------+
    | Val 1   | 15.75                   | 2016-07-07 00:03:03  |
    +---------+-------------------------+----------------------+
    | Val 1   | 16                      | 2016-07-07 00:31:08  |
    +---------+-------------------------+----------------------+
    | Val 2   | 14.75                   | 2016-07-07 15:04:02  |
    +---------+-------------------------+----------------------+

它返回 GROUP BY

    +---------+-------------------------+----------------------+
    | name    | commission_percentage_c |     date_entered     |
    +---------+-------------------------+----------------------+
    | Val 2   | 14.75                   | 2016-07-07 15:04:02  |
    +---------+-------------------------+----------------------+
    | Val 1   | 15.25                   | 2016-07-06 23:57:28  |
    +---------+-------------------------+----------------------+

我想得到的是:

    +---------+-------------------------+----------------------+
    | name    | commission_percentage_c |     date_entered     |
    +---------+-------------------------+----------------------+
    | Val 2   | 14.75                   | 2016-07-07 15:04:02  |
    +---------+-------------------------+----------------------+
    | Val 1   | 16                      | 2016-07-07 00:31:08  |
    +---------+-------------------------+----------------------+

因为我需要返回最近的 VAL 1 记录，比如

WHERE the datediff BETWEEN NOW() and date_entered = the smallest amount of time compared with other records

Answer 1

试试这个

SELECT *
FROM
    (SELECT
        aos_products.`name` as name,
        cac_customize_agent_comp_cstm.commission_percentage_c as commission_percentage_c,
        cac_customize_agent_comp.date_entered as date_entered
    FROM
        wn_writing_number
    LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number.id = wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida
    LEFT OUTER JOIN cac_customize_agent_comp_cstm ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp_cstm.id_c
    LEFT OUTER JOIN cac_customize_agent_comp ON cac_customize_agent_comp_cstm.id_c = cac_customize_agent_comp.id
    LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
    LEFT OUTER JOIN aos_products ON aos_products_cac_customize_agent_comp_1_c.aos_products_cac_customize_agent_comp_1aos_products_ida = aos_products.id
    WHERE
        wn_writing_number.id = 'b556d816-f2e6-b78f-c776-576c0542585c'
    AND wn_writing_number_cac_customize_agent_comp_1_c.deleted = '0'
    AND cac_customize_agent_comp_cstm.effective_date_c <= CURDATE()
    ORDER BY cac_customize_agent_comp.date_entered DESC) t
GROUP BY t.name

这样做的想法是使用 group by 按 date_entered 对记录进行排序。来自 this post , groupby 将使用与正常 SELECT 相同的顺序选择第一个。