mysql - 与min一起使用左联接

我正在试着用左键连接两个表，并指定一个日期。
我的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.'\'' ;

苏令

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

订单历史记录

+----+-------------------+-----------------+---------+---------------------+
| 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 |
+-------------+---------------------+

我遇到的问题是只获取日期，在该日期中，已结算/付款状态id值已按顺序更改。我现在得到所有付款状态id与当前清除值匹配的日期，但我只需要一个，它首先发生的日期。
这只是实际查询的一部分，因为原始查询要长得多（主要是更多的左连接和更多的表）。

最佳答案

您可以按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.'\'';

关于mysql - 与min一起使用左联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32179875/