查询每组代码的前 N ​​个代码适用于 MySQL，但对于 MariaDB 的结果不同

Query for top N per group code works for MySQL but different result for MariaDB(查询每组代码的前 N ​​个代码适用于 MySQL，但对于 MariaDB 的结果不同)

问题描述

I have a SQL query which extracts the latest 3 records of each group. The query result for MySQL is different from MariaDB. This query is implemented in the sqlfiddle below

http://sqlfiddle.com/#!9/c09fe/2

Contents of table

CREATE TABLE tmp
    (`mac_addr` varchar(10), `reader_name` varchar(22), `value` numeric, `time_change` datetime)
;

INSERT INTO tmp
    (`mac_addr`, `reader_name`, `value`, `time_change`)
VALUES
    ('''B99A88''', '''name_8''', 1, '2016-07-07 19:21:48'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-06-21 13:30:00'),
    ('''B99A88''', '''own__temperature_1''', 37.4, '2016-05-04 18:23:03'),
    ('''B99A88''', '''own__temperature_1''', 29.4, '2016-05-04 18:19:33'),
    ('''B99A88''', '''own__temperature_1''', 28.4, '2016-05-04 18:17:32'),
    ('''B99A88''', '''own__temperature_1''', 27.4, '2016-05-04 18:04:08'),
    ('''B99A88''', '''own__temperature_1''', 21.4, '2016-05-04 15:11:42'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-20 15:22:23'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-15 17:39:52'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-15 17:39:46'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-11 17:34:00'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-11 17:33:00'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-11 17:33:00'),
    ('''B99A88''', '''own__temperature_1''', 28.4, '2016-04-10 21:20:20'),
    ('''B99A88''', '''own__temperature_1''', 32.5, '2016-04-10 21:00:00'),
    ('''B99A88''', '''own__temperature_1''', 34.2, '2016-04-10 11:29:00')
;

Query to extract latest 3 records of each group.

SELECT mac_addr, reader_name, value, time_change
FROM (
    SELECT t1.*,
           IF(@rn = reader_name, @rowno := @rowno + 1, @rowno := 1) AS rowno,
           @rn := reader_name
    FROM (
        SELECT *
          FROM tmp
        ORDER BY reader_name, time_change DESC
    ) t1
    CROSS JOIN (SELECT @rn := null, @rowno := 0) t2
) t
WHERE rowno <= 3

The result when using MySQL v5.6 is as below;

mac_addr    reader_name             value   time_change
'B99A88'    'name_8'                 1      July, 07 2016 19:21:48
'B99A88'    'own__detect_1'          1      June, 21 2016 13:30:00
'B99A88'    'own__detect_1'          0      April, 20 2016 15:22:23
'B99A88'    'own__detect_1'          1      April, 15 2016 17:39:52
'B99A88'    'own__temperature_1'    37      May, 04 2016 18:23:03
'B99A88'    'own__temperature_1'    29      May, 04 2016 18:19:33
'B99A88'    'own__temperature_1'    28      May, 04 2016 18:17:32

The MySQL result is what I want. However, I am using MariaDB and the result is different from the MySQL result.

The MariaDB result looks like this;

mac_addr    reader_name             value   time_change
'B99A88'    'name_8'                 1      2016-07-07 19:21:48
'B99A88'    'own__detect_1'          1      2016-06-21 13:30:00
'B99A88'    'own__temperature_1'    37      2016-05-04 18:23:03
'B99A88'    'own__temperature_1'    29      2016-05-04 18:19:33
'B99A88'    'own__temperature_1'    28      2016-05-04 18:17:32
'B99A88'    'own__detect_1'          0      2016-04-20 15:22:23
'B99A88'    'own__detect_1'          1      2016-04-15 17:39:52
'B99A88'    'own__detect_1'          0      2016-04-15 17:39:46
'B99A88'    'own__temperature_1'    28      2016-04-10 21:20:20
'B99A88'    'own__temperature_1'    33      2016-04-10 21:00:00
'B99A88'    'own__temperature_1'    34      2016-04-10 11:29:00

How can the query code be modified such that the query output of MariaDB can be the same as MySQL? Would using window functions in MariaDB be a good idea?

The query execution is allowed to ignore the ORDER BY in the FROM ( SELECT ... ). This probably the real reason for the difference you are seeing. (I don't think Gordon's answer is relevant.)

The problem is discussed here (4 years ago): https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/ ; that has one solution, via a setting.

Some other solutions are here: http://mysql.rjweb.org/doc.php/groupwise_max ; they are designed to be efficient.

Yet another possible solution is to add a bogus LIMIT with a large number on the subquery.

这篇关于查询每组代码的前 N ​​个代码适用于 MySQL，但对于 MariaDB 的结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！