时间戳

首页 > 解决方案 > 使用 SUM 作为检索表之一时，如何保留 LEFT OUTER JOIN 表中的所有值，为什么 GROUP BY 似乎可以解决问题？

sql - 使用 SUM 作为检索表之一时，如何保留 LEFT OUTER JOIN 表中的所有值，为什么 GROUP BY 似乎可以解决问题？

问题描述

我正在尝试从 2 个非常小的客户表及其购买信息中提取信息。这两个表是带有 id、name、email 的“customers”。以及带有 id、customer_id、item、price 的“订单”。

我想要的输出是客户姓名、电子邮件以及他们一生中所有购买的总数。

问题是有些客户从未购买过任何东西或只购买过一次。因此，即使在使用 LEFT OUTER JOIN 合并表并包括每个客户之后，我也只能获得有关进行了多次购买的客户的信息（当 SUM 函数可以工作时）。

当我选择 GROUP BY customers.name 时，这似乎得到了解决。我不知道为什么这实际上有效，并且想知道 LEFT OUTER JOIN、SUM 和 GROUP BY 之间的这种交互是如何工作的。下面是整个信息表以及我为提取信息所做的一切。

GROUP BY 似乎解决了这个问题，但我想知道它为什么真的有效。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT);

INSERT INTO customers (name, email) VALUES ("Doctor Who", "doctorwho@timelords.com");
INSERT INTO customers (name, email) VALUES ("Harry Potter", "harry@potter.com");
INSERT INTO customers (name, email) VALUES ("Captain Awesome", "captain@awesome.com");

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    item TEXT,
    price REAL);

INSERT INTO orders (customer_id, item, price)
    VALUES (1, "Sonic Screwdriver", 1000.00);
INSERT INTO orders (customer_id, item, price)
    VALUES (2, "High Quality Broomstick", 40.00);
INSERT INTO orders (customer_id, item, price)
    VALUES (1, "TARDIS", 1000000.00);

SELECT customers.name, customers.email, SUM(orders.price) AS total
FROM customers LEFT OUTER JOIN
     orders
     ON customers.id = orders.customer_id
GROUP BY customers.name 
ORDER BY total DESC;

标签： sql

解决方案

据推测，您使用的是 MySQL 以及较旧的版本，因为较新版本中的默认设置会给您带来语法错误。

如果您尝试此查询

SELECT c.name, c.email, SUM(o.price) AS total
FROM customers c LEFT OUTER JOIN
     orders o
     ON c.id = o.customer_id
ORDER BY total DESC;

你有一个混蛋的 SQL 查询。查询：

  • 是一个聚合查询（因为SUM()）；
  • 返回一行（因为没有GROUP BY）；
  • 有两个挥之不去的列，c.name并且c.email.

大多数数据库（正确地）拒绝这样的查询。MySQL 恰好允许它（在旧版本中）。c.name和的值c.email来自任意行。SUM()是所有数据的总和。

表达这一点的正确方法是：

SELECT c.name, c.email, SUM(o.price) AS total
FROM customers c LEFT OUTER JOIN
     orders o
     ON c.id = o.customer_id
GROUP BY c.name, c.email
ORDER BY total DESC;

请注意， 中GROUP BY包含未聚合的列SELECT。这会为每个客户姓名/电子邮件生成一行。

因为customers.id是主键，你也可以这样写：

SELECT c.name, c.email, SUM(o.price) AS total
FROM customers c LEFT OUTER JOIN
     orders o
     ON c.id = o.customer_id
GROUP BY c.id
ORDER BY total DESC;

推荐阅读