Mysql变量计数不增加

Question

我有以下 MySQL 查询:

SET @sum1 = 0;
SELECT (@sum1 := @sum1 + 1),date_clicked
FROM `documents_clicks_or_downloads` dclick
JOIN `documents_repository` drep ON dclick.documentID = drep.ID
WHERE accountID = 2304
ORDER BY dclick.ID ASC

以下是我得到的结果:

"(@sum1 := @sum1 + 1)"  "date_clicked"
"1" "2014-04-04"
"2" "2014-04-04"
"3" "2014-04-04"
"4" "2014-04-04"
"5" "2014-04-04"
"6" "2014-04-04"
"7" "2014-04-04"
"8" "2014-04-04"
"9" "2014-04-04"
"10"    "2014-04-04"
"11"    "2014-04-04"
"12"    "2014-04-04"
"13"    "2014-04-04"
"14"    "2014-04-04"
"15"    "2014-04-04"
"16"    "2014-04-04"
"17"    "2014-04-04"
"18"    "2014-04-04"
"19"    "2014-04-04"
"20"    "2014-04-04"
"21"    "2014-04-04"
"22"    "2014-04-04"
"23"    "2014-04-04"
"24"    "2014-04-04"
"33"    "2014-04-04"
"34"    "2014-04-04"
"35"    "2014-04-04"
"36"    "2014-04-04"
"37"    "2014-04-04"
"38"    "2014-04-04"
"39"    "2014-04-04"
"40"    "2014-04-04"
"41"    "2014-04-04"
"42"    "2014-04-04"
"43"    "2014-04-04"
"44"    "2014-04-04"
"45"    "2014-04-04"
"46"    "2014-04-04"
"47"    "2014-04-04"
"48"    "2014-04-04"
"49"    "2014-04-04"
"50"    "2014-04-04"
"51"    "2014-04-04"
"52"    "2014-04-04"
"53"    "2014-04-04"
"54"    "2014-04-04"
"25"    "2014-04-04"
"26"    "2014-04-04"
"27"    "2014-04-04"
"55"    "2014-04-04"
"56"    "2014-04-04"
"57"    "2014-04-04"
"28"    "2014-04-04"
"29"    "2014-04-05"
"58"    "2014-04-05"
"30"    "2014-04-06"
"31"    "2014-04-06"
"59"    "2014-04-06"
"61"    "2014-04-07"
"62"    "2014-04-07"
"63"    "2014-04-07"
"64"    "2014-04-07"
"65"    "2014-04-07"
"66"    "2014-04-07"
"67"    "2014-04-07"
"68"    "2014-04-07"
"69"    "2014-04-07"
"70"    "2014-04-07"
"71"    "2014-04-07"
"72"    "2014-04-07"
"73"    "2014-04-07"
"74"    "2014-04-07"
"75"    "2014-04-07"
"76"    "2014-04-07"
"77"    "2014-04-07"
"87"    "2014-04-07"
"88"    "2014-04-07"
"89"    "2014-04-07"
"90"    "2014-04-07"
"91"    "2014-04-07"
"92"    "2014-04-07"
"93"    "2014-04-07"
"94"    "2014-04-07"
"95"    "2014-04-07"
"96"    "2014-04-07"
"97"    "2014-04-07"
"98"    "2014-04-07"
"99"    "2014-04-07"
"100"   "2014-04-07"
"101"   "2014-04-07"
"102"   "2014-04-07"
"103"   "2014-04-07"
"104"   "2014-04-07"
"105"   "2014-04-07"
"106"   "2014-04-07"
"107"   "2014-04-07"
"78"    "2014-04-07"
"79"    "2014-04-07"
"108"   "2014-04-07"
"109"   "2014-04-07"

所以问题是，为什么我在总和列上没有得到正确的数字增加？它似乎到处跳来跳去，我不知道为什么。谢谢。

Answer 1

问题是选择发生在排序之前。

尝试将其作为子查询来执行:

SET @sum1 = 0;
SELECT (@sum1 := @sum1 + 1), t.*
FROM (
    SELECT
    date_clicked
    FROM `documents_clicks_or_downloads` dclick
    JOIN `documents_repository` drep ON dclick.documentID = drep.ID
    WHERE accountID = 2304
    ORDER BY dclick.ID ASC
) t