Mysql复杂的从4个表中选择查询

Question

我研究了将近 200 个关于 mysql 复杂查询的示例页面，但一直坚持下去。

这是我的结构

Table name: zones

zoneId | zoneName
------------------

Table name: customers

customesId | zoneId | customerName
----------------------------------

Table name: products

productId | productName
-----------------------

Table name: sales

sid | zoneId | customerId | productId | amount
----------------------------------------------

是否可以仅通过查询获得以下输出？

zoneName | customerName | productName   | amount(SUM)
---------------------------------------------------
ZoneX    | customerA    | productName_1 | 10
         |              | productName_2 | 0
         |              | productName_3 | 4
         |              | productName_4 | 0
ZoneX    | customerB    | productName_1 | 7
         |              | productName_2 | 0
         |              | productName_3 | 4
         |              | productName_4 | 3
.......

即使客户或产品没有销售，我也想得到“0”

我试过:

SELECT zones.zoneName
     , customers.customerName
     , products.productName
     , SUM(amount) AS amount 
FROM customers 
INNER JOIN zones 
  ON customers.zoneId = zones.zoneId 
LEFT JOIN sales 
  ON customers.customerId = sales.customerId 
LEFT JOIN products 
  ON sales.productId = products.productId

Answer 1

您需要将所有客户交叉连接到产品，以便每个客户都能列出所有产品，无论销售情况如何。

SELECT z.zoneName
     , c.customerName
     , p.productName
     , SUM(coalesce(s.amount,0)) AS amount 
FROM customers c
INNER JOIN zones z 
  ON c.zoneId = z.zoneId 
CROSS JOIN PRODUCTS P
LEFT JOIN sales S
  ON c.customerId = s.customerId
 and s.productID = p.productID 
GROUP BY z.zoneName
       , c.customerName
       , p.productName