一个 MySQL 列的总和，ID 列的唯一值

Question

一个 MySQL 列的总和，ID 列的唯一值

mysql

5

我正在尝试创建一份订单汇总报告，但在单个查询中提取所有所需数据时遇到了问题。

我希望提取的数据包括：

小计 - 所有销售价格的总和
运费总额 - 所有订单deliveryTotal的总和
订单数 - DISTINCT订单号的数量
数量 - 所有订购数量的总和

订单表（此示例简化）

| orderId | deliveryTotal | total |
|---------|---------------|-------|
| 1       | 5             | 15    |
| 2       | 5             | 15    |
| 3       | 7.50          | 27.50 |

订单商品表

| orderItemId | orderId | productId | salePrice | quantity |
|-------------|---------|-----------|-----------|----------|
| 1           | 1       | 1         | 10        | 1        |
| 2           | 2       | 1         | 10        | 1        |
| 3           | 3       | 1         | 10        | 1        |
| 4           | 3       | 2         | 10        | 1        |

我目前用于提取这些数据的查询语句是：

SELECT
    SUM(i.salePrice * i.quantity) as subtotal,
    SUM(DISTINCT o.deliveryTotal) as deliveryTotal,
    COUNT(DISTINCT o.orderId) as orders,
    SUM(i.quantity) as quantity
FROM orderItems i
INNER JOIN orders o ON o.orderId = i.orderId

这会导致正确的小计、订单数和数量总和。但是，当我想要17.50美元时，运费总额返回12.50美元。如果我使用SUM(o.deliveryTotal)，它将返回25美元。

编辑：期望结果

| subtotal | deliveryTotal | orders | quantity |
|----------|---------------|--------|----------|
| 40.00    | 17.50         | 3      | 4        |

- Nick

你能提供 SQL Fiddle 吗？ - Sadikhasan

@Sadikhasan，这是链接：http://sqlfiddle.com/#!9/02cb0/1 - Michael Berkowski

更新了带有期望结果的问题。 - Nick

这个查询语句会一直应用于整张表吗？还是您期望稍后添加一个 GROUP BY 语句？ - Michael Berkowski

始终是整个表格（将有一个where语句来选择日期之间的内容），但没有分组。 - Nick

4个回答

4

由于进行了连接操作，SUM(DISTINCT deliveryTotal)聚合函数被应用于包含值5、5、7.5、7.5（不重复的值5+7.5=12.5）的行集上。

如果您仅执行SUM()函数，那么该函数作用的行将更加明显。

SELECT o.*
FROM orderItems i
INNER JOIN orders o ON o.orderId = i.orderId

相反，您正在请求对deliveryTotal中所有值的SUM()，而不管它们在与orderItems的连接中的位置如何。这意味着您需要在不同的级别应用聚合。

由于您不打算稍后添加GROUP BY，因此最简单的方法是使用子查询，其目的仅是跨整个表获取SUM()。

SELECT
    SUM(i.salePrice * i.quantity) as subtotal,
    -- deliveryTotal sum as a subselect
    (SELECT SUM(deliveryTotal) FROM orders) as deliveryTotal,
    COUNT(DISTINCT o.orderId) as orders,
    SUM(i.quantity) as quantity
FROM orderItems i
INNER JOIN orders o ON o.orderId = i.orderId

通常不鼓励使用子查询(Subselects)，但它不会造成明显的性能损失，与使用联接(Join)的替代方法没有任何区别。无论如何，必须在现有联接之外进行单独的聚合计算。其他方法将在FROM子句中放置子查询CROSS JOIN，这与我们在子查询中放置的内容相同。性能也是相同的。

- Michael Berkowski

(SQLfiddle对我现在没有响应，无法发布验证结果，但我已经将其加载到我的测试数据库中，它确实有效) - Michael Berkowski

1

在内部选择中选择每个订单，然后将其求和。

Select 
SUM(subtotal) as subtotal,
sum(deliveryTotal) as deliveryTotal,
count(1) as orders,
sum(quantity) as quantity
from (
SELECT 
    SUM(i.salePrice * i.quantity) as subtotal,
    o.deliveryTotal as deliveryTotal,
    SUM(i.quantity) as quantity
FROM orders o
INNER JOIN orderItems i ON o.orderId = i.orderId
group by o.orderId) as sub

- TheBelgarion

0

以下查询结果正好符合您的需求

SELECT SUM(conctable.subtotal),
SUM(conctable.deliveryTotal),
SUM(conctable.orders),
SUM(conctable.quantity) from  
(SELECT SUM(i.salePrice * i.quantity) as subtotal,
    o.deliveryTotal as deliveryTotal,
    COUNT(DISTINCT o.orderId) as orders,
    SUM(i.quantity) as quantity
FROM orderItems i
JOIN orders o ON o.orderId = i.orderId group by i.orderid) as conctable;

- Thanga

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GarryOne · Accepted Answer

这是一篇关于MySQL左连接下唯一行求和的博客文章，链接如下：https://tiaashish.wordpress.com/2014/01/31/mysql-sum-for-distinct-rows-with-left-join/ 以下是公式： SUM(o.deliveryTotal) * COUNT(DISTINCT o.orderId) / COUNT(*) 希望这篇博客能够对其他人有所帮助。