mysql - 使用 left join with min

Question

我正在尝试使用左连接和日期连接两个表。

我的 SQL 查询

SELECT
    ord.`ordernumber` bestellnummer,
    his.`change_date` zahldatum
FROM
    `s_order` ord
LEFT JOIN
    `s_order_history` his ON ((ord.`id`=his.`orderID`) AND (ord.`cleared`=his.`payment_status_id`)) #AND MIN(his.`change_date`)
WHERE
    ord.`ordertime` >= \''.$dateSTART.'\' AND ord.`ordertime`  <= \''.$dateSTOP.'\'' ;

s_order

+----+---------------------+---------+-------------+
| id |     ordertime       | cleared | ordernumber |
+----+---------------------+---------+-------------+
|  1 | 2014-08-11 19:53:43 |       2 |         123 |
|  2 | 2014-08-15 18:33:34 |       2 |         125 |
+----+---------------------+---------+-------------+

s_order_history

+----+-------------------+-----------------+---------+---------------------+
| id | payment_status_id | order_status_id | orderID | orderID change_date |
+----+-------------------+-----------------+---------+---------------------+
|  1 |                 1 |               5 |       1 | 2014-08-11 20:53:43 |
|  2 |                 2 |               5 |       1 | 2014-08-11 22:53:43 |
|  3 |                 2 |               7 |       1 | 2014-08-12 19:53:43 |
|  4 |                 1 |               5 |       2 | 2014-08-15 18:33:34 |
|  5 |                 1 |               6 |       2 | 2014-08-16 18:33:34 |
|  6 |                 2 |               6 |       2 | 2014-08-17 18:33:34 |
+----+-------------------+-----------------+---------+---------------------+

想要的结果:

+-------------+---------------------+
| ordernumber |     change_date     |
+-------------+---------------------+
|         123 | 2014-08-11 22:53:43 |
|         125 | 2014-08-17 18:33:34 |
+-------------+---------------------+

我遇到的问题是只获取日期，其中 cleared/payment_status_id 值已在 s_order 中更改。我目前获得 payment_status_id 与当前清算值匹配的所有日期，但我只需要它最先发生的日期。

这只是实际查询的摘录，因为原始查询要长得多(主要是更多的左连接和更多的表)。

Answer 1

您可以按ordernumber 对数据进行分组>

SELECT
    ord.`ordernumber` bestellnummer,
    MIN(his.`min_change_date`) as zahldatum
FROM
    `s_order` ord
LEFT JOIN
    `s_order_history` his ON ((ord.`id`=his.`orderID`) AND (ord.`cleared`=his.`payment_status_id`)) #AND MIN(his.`change_date`)
WHERE
    ord.`ordertime` >= \''.$dateSTART.'\' AND ord.`ordertime`  <= \''.$dateSTOP.'\''
GROUP BY 
    ord.`ordernumber`;

或者您可以在子查询中对数据进行分组:

SELECT
    ord.`ordernumber` bestellnummer,
    his.`min_change_date` zahldatum
FROM
    `s_order` ord
LEFT JOIN (
    SELECT
       orderID, payment_status_id, MIN(change_date) as min_change_date
    FROM
        s_order_history
    GROUP BY
        orderID, payment_status_id
) his ON (ord.`id` = his.`orderID` AND ord.`cleared` = his.`payment_status_id`)
WHERE
    ord.`ordertime` >= \''.$dateSTART.'\' AND ord.`ordertime`  <= \''.$dateSTOP.'\'';