mysql - MySQL - 按最低差异搜索日期范围和分组

Question

我需要找到尽可能接近人们正在搜索的所需日期范围的所有对象的一个​​价格。一个对象在不同的​​日期范围内可以有多个价格。

价格表

ID	对象标识	价格	开始	结尾
1	21	50	2021-01-01	2021-04-01
2	21	60	2021-04-02	2021-08-01
3	22	30	2021-01-01	2021-04-01
4	23	150	2021-01-01	2021-04-01
5	21	20	2021-08-02	2021-09-01
6	23	120	2021-07-01	2021-08-01

因此，假设人们正在搜索2021-05-01，2021-06-01结果应该是：

ID	对象标识	价格
2	21	60
3	22	30
6	23	120

我正在处理这个查询，但在某个地方我失去了焦点。

SELECT p.objectid,
    p.id
    p.start,
    p.end,
    p.price,
    ABS(DATEDIFF(p.start, '2021-05-01') + DATEDIFF(p.end, '2021-06-01')) diff
FROM
    prices AS p1
INNER JOIN(
    SELECT
        p.objectid,
        MIN(
            ABS(
                DATEDIFF(start, '2021-05-01') + DATEDIFF(end, '2021-06-01')
            )
        ) AS diff
    FROM
        prices p
    GROUP BY
        p.objectid
) p2
ON
    p1.objectid = p2.objectid AND diff = p2.diff

标签： mysqljoingroup-bydatediff

Answer 1

您可以通过自我加入来实现这一点。
试试下面的查询

SELECT
    Y.id,
    Y.objectid,
    Y.price
FROM
    (
    SELECT
        *,
        ABS(
            DATEDIFF(`start`, '2021-05-01') + DATEDIFF(`end`, '2021-06-01')
        ) AS `diff`
    FROM
        price
) AS Y
INNER JOIN(
    SELECT
        *,
        MIN(
            ABS(
                DATEDIFF(`start`, '2021-05-01') + DATEDIFF(`end`, '2021-06-01')
            )
        ) AS diff
    FROM
        price
    GROUP BY
        objectid
) AS X
ON
    X.objectid = Y.objectid AND X.diff = Y.diff
ORDER BY
    Y.id

o/p 是这个