mysql - SQL 一次性获取总和和所有行

Question

我试图同时获取列数和所有行数的总和。

SELECT sum(orders.amount) as total, orders.transactionID, orders.itemid, products.id, 
products.category, products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'

上面的查询只给我总值(value)和只有一行(我也想拥有所有行)

SELECT sum(orders.amount) as total, orders.transactionID, orders.itemid, products.id, 
products.category, products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'
GROUP BY orders.id

虽然第二个查询给了我每一行，但 orders.amount 的总数不正确

这可以做到吗？

(我将两个查询缩短了一点)

谢谢

Answer 1

您可以使用“运行总计”，即使用变量并自行汇总值。正确的总和将出现在结果集的最后一行。

SELECT @my_sum := @my_sum + orders.amount as running_total, 
orders.transactionID, 
orders.itemid, 
products.id, 
products.category, 
products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
CROSS JOIN (SELECT @my_sum:=0) AS var_init_subquery
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'

• 了解有关用户变量的更多信息 here

另请参阅 Raymond Nijland 的评论:

Query 1.. SUM without GROUP BY always generates 1 row. Query 2 is a invalid use off GROUP BY read https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/