mysql - 如何在不搞砸第一个表中的总值的情况下对连接表中的值求和？

Question

我想SELECT 客户数量、客户订单总和以及特定州的客户数量。

我知道如何在两个查询中轻松执行此操作，但是将使用相同的 WHERE 约束，因此似乎最好在一个查询中执行此操作并避免重复。我很想改进我的 SQL，但我不知道如何组合它们。将它们作为两个单独的查询感觉非常笨拙。

有没有办法把它们结合起来？我应该考虑哪些因素来确定将它们结合起来是否是一个好主意？

---

客户表

*-------------*-------------*--------------*------------*
| ID_Customer | ID_State    | Name         |  ...etc... |
*-------------*-------------*--------------*------------*

状态表

*-------------*-------------*
| ID_State    | Name        |
*-------------*-------------*

订单表

*----------*-------------*--------------*------------*
| ID_Order | ID_Customer |   ...etc...  | Total      |
*----------*-------------*--------------*------------*

---

查询 1.1 - 选择 Count of Customers 和 Count of Customers in specific states

SELECT 
COUNT(*) AS Customers, 
SUM(States.Name = 'California') AS California_Customers, 
SUM(States.Name = 'New York') AS NewYork_Customers 

FROM Customers 
INNER JOIN States ON Customers.ID_State = States.ID_State 

查询 1.2 - 选择客户订单总和

SELECT 
SUM(Total) AS SumOfOrderTotals

FROM Orders 
INNER JOIN Customers ON Customers.ID_Customer = Orders.ID_Customer 

---

查询 2 - 尝试将查询合并为一个(无效)

SELECT 
COUNT (DISTINCT(Customers.ID_Customer)) AS Customers, 
SUM (Orders.Total) AS SumOfOrderTotals, 
SUM (States.Name = 'California') AS California_Customers, 
SUM (States.Name = 'New York') AS NewYork_Customers 

FROM 
Customers 
INNER JOIN Orders ON Customers.ID_Customer = Orders.ID_Customer 
INNER JOIN States ON Customers.ID_State = States.ID_State 

显然这不起作用，因为 Customers 和 Orders 之间的 INNER JOIN 意味着 States.Name 为每个客户计算了 xN(其中 N 是客户的订单数量)，因此这些总数是错误的。

---

我考虑过子查询，但我不确定在这种情况下如何应用子查询(如果那是我应该做的)。

Answer 1

您需要在join 之前进行聚合或使用子查询:

SELECT COUNT(DISTINCT(c.ID_Customer)) AS Customers, 
       o.SumOfOrderTotals, 
       SUM(s.Name = 'California') AS California_Customers, 
       SUM(s.Name = 'New York') AS NewYork_Customers 
FROM Customers c JOIN
     States s
     ON c.ID_State = s.ID_State CROSS JOIN
     (SELECT SUM(Total) as SumOfOrderTotals
      FROM Orders o
     ) o;

你也可以这样写:

SELECT COUNT(DISTINCT(c.ID_Customer)) AS Customers, 
       (SELECT SUM(Total)
        FROM Orders o
       ) as SumOfOrderTotals, 
       SUM(s.Name = 'California') AS California_Customers, 
       SUM(s.Name = 'New York') AS NewYork_Customers 
FROM Customers c JOIN
     States s
     ON c.ID_State = s.ID_State;