mysql - 连接与子查询

Question

我需要这样做，但使用子查询，而不是联接。我的问题是，如何使用子查询来显示另一列？我可以从那里获取信息，但我会丢失订单表中的 order_date 列。我可以使用子查询来显示它吗？

SELECT CONCAT(c.customer_first_name, ' ' , c.customer_last_name) AS customer_name, MAX(o.order_date) AS recent_order_date
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY customer_name
ORDER BY MAX(o.order_date) DESC

Answer 1

根本不清楚您要返回什么结果集，但它看起来很像无处不在的“最新行”问题。

解决该问题的规范模式是使用 JOIN 到内联 View 。如果没有唯一约束，则有可能返回多个匹配行。

获取最新订单(orders 表中每个客户具有最大 order_date 的行，假设 (customer_id, order_date) tuple 是唯一的，你可以这样做:

SELECT o.*
  FROM ( SELECT n.customer_id
              , MAX(n.order_date) AS latest_order_date
           FROM orders n
          GROUP BY n.customer_id
       ) m
  JOIN orders o
    ON o.customer_id = m.customer_id
   AND o.order_date = m.latest_order_date

如果您还想根据订单返回的 customer_id 从 customers 表中检索列，则可以使用 JOIN(而不是子查询)

SELECT CONCAT(c.customer_first_name,' ',c.customer_last_name) AS customer_name
     , c.whatever
     , o.order_date AS recent_order_date
     , o.whatever
  FROM ( SELECT n.customer_id
              , MAX(n.order_date) AS latest_order_date
           FROM orders n
          GROUP BY n.customer_id
       ) m
  JOIN orders o
    ON o.customer_id = m.customer_id
   AND o.order_date = m.latest_order_date
  JOIN customers c
    ON c.customer_id = o.customer_id
 ORDER BY o.order_date DESC, o.customer_id DESC

<小时/>

正如我之前提到的，如果给定客户可以有两个 order_date 值完全相同的订单，则每个 customer_id 可能会返回多个订单。

为了纠正这个问题，我们可以从内联 View 返回一个唯一键，并在连接谓词中使用它来保证仅从订单返回一行。

(注意:这种方法是 MySQL 特有的，使用这种语法，其他 RDBMS 会抛出一个错误，本质上是“GROUP BY 必须包含所有非聚合”。但 MySQL 允许这样做。)

SELECT CONCAT(c.customer_first_name,' ',c.customer_last_name) AS customer_name
     , c.whatever
     , o.order_date AS recent_order_date
     , o.whatever
  FROM ( SELECT n.customer_id
              , MAX(n.order_date) AS latest_order_date
              , n.order_id
           FROM orders n
          GROUP BY n.customer_id
       ) m
  JOIN orders o
   AND o.customer_id = m.customer_id
   AND o.order_date = m.latest_order_date
   AND o.order_id = n.order_id
  JOIN customers c
    ON c.customer_id = o.customer_id
 ORDER BY o.order_date DESC, o.customer_id DESC