問(wèn)題描述
我有這個(gè) MySQL 查詢(xún):
I have this MySQL query:
SELECT DAYOFYEAR(`date`) AS d, COUNT(*)
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d
返回如下內(nèi)容:
d | COUNT(*) |
20 | 5 |
21 | 7 |
22 | 12 |
23 | 4 |
我真正想要的是最后的另一列來(lái)顯示運(yùn)行總數(shù):
What I'd really like is another column on the end to show the running total:
d | COUNT(*) | ??? |
20 | 5 | 5 |
21 | 7 | 12 |
22 | 12 | 24 |
23 | 4 | 28 |
這可能嗎?
推薦答案
也許對(duì)您來(lái)說(shuō)是一個(gè)更簡(jiǎn)單的解決方案，并且可以防止數(shù)據(jù)庫(kù)執(zhí)行大量查詢(xún).這僅執(zhí)行一個(gè)查詢(xún)，然后在一次傳遞中對(duì)結(jié)果進(jìn)行一些數(shù)學(xué)運(yùn)算.
Perhaps a simpler solution for you and prevents the database having to do a ton of queries. This executes just one query then does a little math on the results in a single pass.
SET @runtot:=0;
SELECT
q1.d,
q1.c,
(@runtot := @runtot + q1.c) AS rt
FROM
(SELECT
DAYOFYEAR(`date`) AS d,
COUNT(*) AS c
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d) AS q1
這將為您提供一個(gè)額外的 RT(運(yùn)行總計(jì))列.不要錯(cuò)過(guò)頂部的 SET 語(yǔ)句來(lái)首先初始化運(yùn)行總計(jì)變量，否則您只會(huì)得到一列 NULL 值.
This will give you an additional RT (running total) column. Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.
這篇關(guān)于在 MySQL 中計(jì)算運(yùn)行總數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！