sqlfiddle.com 5.5.30和MariaDB 5.5.31中的结果不同 [英] Different results in sqlfiddle.com 5.5.30 and MariaDB 5.5.31
问题描述
sqlfiddle: http://sqlfiddle.com/#!2/9a8b3/1
sqlfiddle: http://sqlfiddle.com/#!2/9a8b3/1
从小提琴中获取结构,数据和查询, 导入我的MariaDB 5.5.31,我得到不同的结果:
Taking the structure and data and query from the fiddle, importing into my MariaDB 5.5.31, I get different results:
sqlfiddle
sqlfiddle
PID NAME LEAGUEPOINTS TOTALLEAGUEPOINTS
2 Peter 16,13,9,4,2 44
1 Daniel 3425,543,234,43,29,22,21,21,19,17,13,12,12,12,11,9,9,9,8,7 4476
mariadb
pid name leaguepoints totalleaguepoints
2 Peter 16,13,9,4,2 44
1 Daniel 3425,543,234,43,29,22,21,21,19,17,13,12,12,12,11,9,9,9,8,7,7,6,5,5,4,4,4,3,3,2,1 4520
查询:
SELECT
p.pid,
p.name,
GROUP_CONCAT( gC.leaguepoints ORDER BY leaguepoints DESC ) AS leaguepoints,
SUM(gC.leaguepoints) AS totalleaguepoints
FROM test_golf_player p
LEFT JOIN
(
SELECT pid, leaguepoints, @Sequence:=IF(@PrevPid = pid, @Sequence + 1, 0) AS aSequence, @PrevPid := pid
FROM
(
SELECT pid, leaguepoints
FROM test_golf_card
ORDER BY pid, leaguepoints DESC
) Sub1
CROSS JOIN (SELECT @PrevPid := 0, @Sequence := 0) Sub2
) gC
ON p.pid = gC.pid AND aSequence < 20
GROUP BY p.pid
ORDER BY p.name DESC
知道为什么吗?
推荐答案
怕我没有MariaDB,但是您可以尝试以下操作来查看用户变量的输出方式吗:-
Afraid I don't have MariaDB to hand, but could you try the following just to see how the user variables are output:-
SELECT *
FROM test_golf_player p
LEFT JOIN
(
SELECT pid, leaguepoints, @Sequence:=IF(@PrevPid = pid, @Sequence + 1, 0) AS aSequence, @PrevPid := pid
FROM
(
SELECT pid, leaguepoints
FROM test_golf_card
ORDER BY pid, leaguepoints DESC
) Sub1
CROSS JOIN (SELECT @PrevPid := 0, @Sequence := 0) Sub2
) gC
ON p.pid = gC.pid
ORDER BY p.name DESC
编辑-做一些调查以查看您的结果,看来MariaDB忽略了子查询中的ORDER BY.因此,序列号按随机顺序排列,并且在pid更改时也会重置(由于顺序不固定,它会随机执行).有点谷歌,这似乎是MariaDB的蓄意功能. SQL标准将表定义为无序的行集,并且将子选择视为表,因此将忽略排序依据-
EDIT - Doing a bit of investigation looking at your results it seems that MariaDB has ignored the ORDER BY in the sub query. Hence the sequence number is in a random order, and also resets when the pid changes (which it does randomly due to the order not being fixed). Bit of a google and it seems this is a deliberate feature of MariaDB. The SQL standard defines a table as an unordered set of rows, and a sub select is treated as a table hence the order by is ignored - https://kb.askmonty.org/en/why-is-order-by-in-a-from-subquery-ignored/ .
这是一个缺点.不确定是否有解决方法,因为我暂时无法想到.对于最初要解决的问题,我认为有必要使用可能不太有效的相关子选择.
It is a bit of a disadvantage. Not sure there is a work around as I can't think of one at the moment. For the original problem that this was to deal with I think it would be necessary to use correlated sub selects which would probably not be efficient.
这篇关于sqlfiddle.com 5.5.30和MariaDB 5.5.31中的结果不同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!