mysql - 加入VS子查询

我需要执行此操作，但要使用子查询而不是联接。我的问题是，如何使用子查询显示另一列？我可以从那里获取信息，但是我会在订单表中缺少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

最佳答案

尚不清楚您要返回什么结果集，但看起来像无处不在的“最新行”问题一样可怕。

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

要获取最新订单（假设每个orders元组是唯一的，order_date表中具有最大(customer_id, order_date)的行），您可以执行以下操作：

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

如果您还想根据订单返回的customers从customer_id表中检索列，则可以使用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都有可能返回多个订单。

为了解决这个问题，我们可以从内联视图返回唯一键，并在连接谓词中使用该键以保证仅从订单返回的一行。

（注意：这种方法是特定于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

关于mysql - 加入VS子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22313235/