mysql - 优化 Sql Query 删除 SELECT 子句并使用 JOIN

Question

不是 SQL 专家

我正在尝试提出一个 SQL 查询来对 4 个表进行 JOIN。我正在使用 MySQL，我有一个有效的查询，但它需要优化。

这些是我的四个表，如图所示:

客户

+--------------+---------+------+-----+---------+----------------+-------+
| id        | first_name    | last_name | email | zip | phone | type_id  |
+--------------+---------+------+-----+---------+----------------+-------+

顺序

+--------------+---------+---
| id        | product_name   |
+--------------+---------+---

客户订单

+--------------+---------+
| customer_id  | order_id|
+--------------+---------+

客户类型

+--------------+----
| type_id   | type | 
+--------------+----

解决方案一:

这是我可以想出并有效的 mysql 查询。但它中间有一个 Select 子句，我不喜欢它。我想用 JOIN 替换它并尝试了很多东西，但出现语法错误。

SELECT DISTINCT c.id AS cus_id,
                c.email,
                c.phone,
                c.zip,
                c.last_name,
                c.first_name,
                coo.product_name,
                ct.type AS cus_type
FROM customer c
LEFT OUTER JOIN
  (SELECT o.product_name,
          co.customer_id
   FROM customer_order co,
   order o
   WHERE co.customer_id = o.id) coo ON coo.customer_id = c.id
JOIN customer_type ct ON ct.type_id = c.type_id

解决方案 2:

我重构了上面的查询，将 WHERE 条件部分替换为 JOIN ，但这仍然与上面的查询相同，按我想要的方式工作到。

SELECT DISTINCT c.id AS cus_id,
                c.email,
                c.phone,
                c.zip,
                c.last_name,
                c.first_name,
                coo.product_name,
                ct.type AS cus_type
FROM customer c
LEFT OUTER JOIN
  (SELECT o.product_name,
          co.customer_id
   FROM customer_order co
   JOIN
   order o ON co.customer_id = o.id) coo ON coo.customer_id = c.id
JOIN customer_type ct ON ct.type_id = c.type_id

有人可以指导我如何用 JOIN 替换我在两者之间使用的 SELECT 查询。

感谢您的帮助。

Answer 1

试试这个…………

SELECT c.id AS cus_id, c.email, c.phone, c.zip, 
c.last_name, c.first_name, o.product_name, ct.type AS cus_type
FROM customer c
INNER JOIN customer_type ct ON ct.type_id = c.type_id
LEFT  JOIN customer_order co ON co.customer_id = c.id
LEFT  JOIN order o ON co.order_id = o.id