如何在新列中添加联合所有结果

Question

我想做一个查询，其中我有每个设备的总数:数量*成本。

现在，我想要执行一个MySQL查询，其中我创建了一个执行以下操作的临时列：

Total - Total of Totals
80    -  80
90    -  170
100   -  270

现在我有一个这样的代码：

Select total
from equipment
UNION ALL
select sum(total) from equipment;

它给出了总数下面的总数。我想把它分开，然后在它旁边按顺序做。

Answer 1

在MySQL8中，可以使用窗口函数来实现这一点

WITH CTE AS (
SELECT
   quantity * costs as total as total
FROM 
equipment)
    select
      total,
      sum(total) over (order by total) as cumulative_sum
    from CTE;

Answer 2

使用GROUP BY和ROLLUP

SELECT id, SUM(quantity * costs) AS total
FROM equipment
GROUP BY id WITH ROLLUP;

在结果集的底部将出现一条id为NULL的记录，其总数将包含在所有组中。

Answer 3

您似乎想要一个窗口函数，如下所示：

select (quantity * costs) as total,
       sum(quantity * costs) over (order by quantity * costs) as running_total
from equipment;

实际上，我希望您现有的总数是聚合的结果。您的问题不清楚是什么，但您可以将窗口函数与聚合函数一起使用：

select sum(quantity * costs) as total,
       sum(quantity * costs) over (order by sum(quantity * costs)) as running_total
from equipment
group by <something>