如何使用带条件的sql连接多(四)个表

Question

我正在尝试创建一个SQL查询，该查询有条件地从多个表中提取数据。

我有四张桌子：

订单

+------+------------+------------+
| id   | date_added | currency   |
+------+------------+------------+
|   1  | 2018-07-23 |          1 |
+------+------------+------------+

order_items

+------+------------+------------+---------------+---------------+
| id   | order_id   | price      | product_id    | product_type  |
+------+------------+------------+---------------+---------------+
|   1  | 1          | 100.00     |          1    |   ticket      |
+------+------------+------------+---------------+---------------+

订单_数据

+------+--------------+---------------+
| id   | order_id     | ext_order_ref |
+------+--------------+---------------+
|   1  | 1            |         ABC   |
+------+--------------+---------------+

产品

+------+------------+------------+
| id   | date       | product_id |
+------+------------+------------+
|   1  | 2020-03-12 |          1 |
+------+------------+------------+
|   2  | 2020-03-18 |          2 |
+------+------------+------------+
|   3  | 2020-03-20 |          3 |
+------+------------+------------+

我需要在以下条件下输出订单：

• 一行中的每个订单(根据具有匹配订单id的订单项计算(
• order_data表中与该订单匹配的"ext_order_ref">
• 仅包括具有特定产品类型的订单项
• 仅包括特定日期范围内的产品订单

首选输出如下所示：

+------------+------------+--------------+
| order_id   | total      | ext_order_ref|
+------------+------------+--------------+
|   1        | 100        |          ABC |
+------------+------------+--------------+

我当前的查询基本上是这样的；请告知

SELECT
orders.id as order_id,
SUM(order_items.price) as total,
order_data.ext_order_ref
FROM orders
INNER JOIN order_data
ON orders.id = order_data.ext_order_ref
RIGHT JOIN order_items
ON orders.id = order_items.order_id
LEFT JOIN products
ON order_items.product_id = products.product_id
WHERE order_items.product_type = 'ticket' AND products.date BETWEEN '2020-03-12' AND '2020-03-18'
GROUP BY orders.id

它几乎起作用，但不完全起作用。日期尤其引起争议。

提前感谢！

Answer 1

首先确定查询的驱动表，并根据驱动表形成查询。驱动表是连接其他表的一个主表。

关于驾驶台的更多信息，请访问askTom

在您的情况下，驱动表是Orders表。您正在RIGHT OUTER JOIN和LEFT OUTER JOIN之间切换。这将导致结果集中出现混乱。

我已经修改了查询。看看它是否有效。

SELECT
orders.id as order_id,
SUM(order_items.price) as total,
order_data.ext_order_id
FROM orders
INNER JOIN order_data
ON orders.id = order_data.ext_order_id
LEFT OUTER JOIN order_items
ON orders.id = order_items.order_id
LEFT OUTER JOIN products
ON order_items.product_id = products.product_id
WHERE order_items.product_type = 'ticket' AND products.date BETWEEN '2020-03-12' AND '2020-03-18'
GROUP BY orders.id

Answer 2

我不明白你为什么想要外部联接。如果我正确遵循条件：

SELECT o.id as order_id,
SUM(oi.price) as total,
od.ext_order_id
FROM orders o INNER JOIN
order_data od
ON o.id = od.ext_order_id INNER JOIN
order_items oi
ON o.id = oi.order_id INNER JOIN
products p
ON oi.product_id = p.product_id
WHERE oi.product_type = 'ticket' AND
p.date >= '2020-03-12' AND 
p.date < '2020-03-19'
GROUP BY o.id, od.ext_order_id;

请注意表别名的使用，这样查询更易于编写和读取。