时间戳

首页 > 解决方案 > 如何按选定的顺序选择所有具有 min begin_date 和 max end_date 的工人？

mysql - 如何按选定的顺序选择所有具有 min begin_date 和 max end_date 的工人？

问题描述

为了“测试”只有两个工人：

——亚当·盖克斯——安德鲁·沃姆

当我编写一个 select sql 查询时：

SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE) FROM order_status 
INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER 
WHERE orders.NUMBER_ORDER = 'TEST';

我知道 Min 的值是多少。BEGIN_DATE 和最大值。END_DATE 为“TEST”订单：

对于工人亚当盖克斯：

对于工人 Andrew Worm

我尝试了两种方式：

1) 带有FULL JOIN- 表示LEFT JOIN, UNION ALL, RIGHT JOIN:

SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE), workers.FNAME, workers.LNAME 
FROM order_status 
LEFT JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER
INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER
WHERE orders.NUMBER_ORDER = 'TEST'
UNION ALL
SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE), workers.FNAME, workers.LNAME 
FROM order_status
RIGHT JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER
INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER
WHERE orders.NUMBER_ORDER = 'TEST';

然后我得到了结果，但它只显示了 1 个工人：

+----------------------------+--------------------------+--------+-------+
|MIN(order_status.BEGIN_DATE)|MAX(order_status.END_DATE)|  FNAME | LNAME | 
+----------------------------+--------------------------+--------+-------+
| 2019-03-18 06:45:11        |   2019-03-22 12:22:33    | Adam   | Gax   |
+----------------------------+--------------------------+----------+-----+
| 2019-03-18 06:45:11        |   2019-03-22 12:22:33    | Adam   | Gax   |
+----------------------------+--------------------------+----------+-----+

2) 我也在尝试FULL OUTER JOIN- LEFT OUTER JOIN, UNION ALL, RIGHT OUTER JOIN:

(SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE), workers.FNAME, workers.LNAME 
FROM order_status 
LEFT OUTER JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER
INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER
WHERE orders.NUMBER_ORDER = 'TEST') 
UNION ALL 
(SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE), workers.FNAME, workers.LNAME 
FROM order_status
RIGHT OUTER JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER
INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER
WHERE zorders.NUMBER_ORDER = 'TEST');

但是与第一次尝试查询的效果相同。我会得到应该得到 min.BEGIN_DATE 和 max.END_DATE 的结果：

+----------------------------+--------------------------+--------+-------+
|MIN(order_status.BEGIN_DATE)|MAX(order_status.END_DATE)|  FNAME | LNAME | 
+----------------------------+--------------------------+--------+-------+
| 2019-03-18 06:50:35        |   2019-03-22 12:22:33    | Adam   | Gax   |
+----------------------------+--------------------------+--------+-------+
| 2019-03-18 06:45:11        |   2019-03-22 11:23:45    | Andrew | Worm  |
+----------------------------+--------------------------+--------+-------+

可能需要更多的工人，但我想专注于这个案子。

现在我有一个问题：如何按选定的顺序选择所有具有 min begin_date 和 max end_date 的工人？提前谢谢。

标签： mysqlsqljoinselect

解决方案

我会从表开始加入workers。
然后你需要GROUP BY workers.FNAME, workers.LNAME：

SELECT MIN(os.BEGIN_DATE), MAX(os.END_DATE), w.FNAME, w.LNAME 
FROM workers w 
INNER JOIN order_status os ON w.ID_WORKER = os.ID_WORKER
INNER JOIN orders o ON o.ID_ORDER = os.ID_ORDER 
WHERE o.NUMBER_ORDER = 'TEST'
GROUP BY w.FNAME, w.LNAME

由于您使用表的WHERE子句orders，我认为不需要LEFT连接。

我还为表格使用了别名，这是一种常见的良好做法。

推荐阅读