mysql查询左连接+计数

Question

我想要一组来自订单文件的数据，这些数据可以为我提供客户编号、订单编号、产品、数量、价格以及每个订单的订单详细信息文件中的行数。我在最后一部分遇到问题。

Select Header.CustNo, Header.OrderNumber, Detail.SKU, 
   Detail.Quantity, Detail.Price, Count(Detail2) from Header
   left join Detail on (Header.OrderNumber= Detail.OrderNumber )
   left join Detail as Detail2 on (Header.OrderNumber= Detail2.OrderNumber )

Answer 1

你是这个意思吗？ :

SELECT Header.CustNo,
       Header.OrderNumber,
       Detail.SKU, 
       Detail.Quantity,
       Detail.Price,
       ( SELECT COUNT(1)
           FROM Detail AS Detail2
          WHERE Detail2.OrderNumber = Header.OrderNumber
       )
  FROM Header
  LEFT
  JOIN Detail
    ON Header.OrderNumber = Detail.OrderNumber
;