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

从小提琴中获取结构和数据并查询，导入我的 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

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 标准将表定义为一组无序的行，子选择被视为表，因此忽略 order by - https://kb.askmonty.org/en/why-is-order-by-in-a-from-subquery-ignored/ .

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屋！