MySQL 查询 - 使用自连接查找最接近开始日期和结束日期的行

Question

我有以下查询，该查询使用一个表来查找最接近日期范围任一端(即最接近日期开始的行)的帐号列表的收入列范围，以及最接近末尾的一个。

我想出了以下方法，但它似乎只适用于第一个帐号，而不适用于此后的任何其他帐号。

SELECT a.acct_no,
b.revenue AS first_value, b.created_at AS first_date,
c.revenue AS second_value, c.created_at AS second_date

FROM `revenue_figures` a

LEFT JOIN (
    SELECT `acct_no`, `revenue`, `created_at`
    FROM  `revenue_figures`
    WHERE `created_at` BETWEEN '2017-02-28 00:00:00' AND '2017-03-31 23:59:59'
    AND `acct_no` IN ('A123', 'A124')
    GROUP BY `acct_no`, `revenue`, `created_at`
    ORDER BY `created_at` ASC
    LIMIT 1
) b ON a.acct_no = b.acct_no

LEFT JOIN (
    SELECT `acct_no`, `revenue`, `created_at`
    FROM  `revenue_figures`
    WHERE `created_at` BETWEEN '2017-02-28 00:00:00' AND '2017-03-31 23:59:59'
    AND `acct_no` IN ('A123', 'A124')
    GROUP BY `acct_no`, `revenue`, `created_at`
    ORDER BY `created_at` DESC
    LIMIT 1
) c ON a.acct_no = c.acct_no

WHERE a.`created_at` BETWEEN '2017-02-28 00:00:00' AND '2017-03-31 
23:59:59'
AND a.`acct_no` IN ('A123', 'A124')

GROUP BY a.acct_no, first_value, first_date, second_value, second_date

这会产生以下结果，但表中有 A124 帐号的数字:

acct_no first_value first_date  second_value    second_date
A123    8990.00     2017-02-28  7364.80         2017-03-31 01:00:00
A124    NULL        NULL        NULL            NULL

我认为该查询存在一个根本缺陷，因为我注意到如果运行几次，我可以生成不同的结果，例如:

acct_no first_value first_date  second_value    second_date
A123    8990.00     2017-02-28  NULL            NULL
A124    NULL        NULL        28361.76        2017-03-31 01:00:00

表结构

id, acct_no, revenue, created_at

示例数据

1, A123, 8990.00, 2017-02-28 01:00:00
2, A123, 7364.80, 2017-03-31 01:00:00
3, A124, 17324.12, 2017-02-28 01:00:00
4, A124, 28361.76, 2017-03-31 01:00:00

Answer 1

(重新打开是因为OP的代码处理了“dup”问题所讨论的内容。真正的问题是关于另一个问题。)

问题是您请求两个 acct_nos 中最新的一个，但随后又请求 LIMIT 1，这会抛出其中一个。让我们首先关注一个子查询。

我是否正确地说您需要 2 行，每个 acct_no 一行？为此，您需要为每个 acct_no 查找 1 行。也许唯一的方法是使用两个 SELECT ... ORDER BY ... LIMIT 1 的 UNION ALL。没有GROUP BY。

所以，现在你已经

LEFT JOIN
(
    ( SELECT ... acct_no = 'A123' ... LIMIT 1 )
    UNION ALL
    ( SELECT ... acct_no = 'A124' ... LIMIT 1 )
)

首先首次使用 UNION 来查看它是否获得了所需的行对。

然后将其放入每个LEFT JOIN(虽然我不知道你是否需要LEFT)，看看大查询是否到来输出确定。

其他一些提示...

INDEX(acct_no, created_at)

使用此模式:

    created_at >= '2017-03-01'
AND created_at  < '2017-03-01' + INTERVAL 1 MONTH

您所拥有的内容包括二月的最后一天，以及三月的全部时间；这是故意的吗？而且，created_at 是 DATETIME 吗？或者其他一些数据类型？

外部 GROUP BY 应该只是

GROUP BY acct_no