对于MySQL,每组代码的前N个查询有效,但对于MariaDB,结果不同 [英] Query for top N per group code works for MySQL but different result for MariaDB
问题描述
我有一个SQL查询,该查询提取每个组的最新3条记录. MySQL的查询结果与MariaDB不同.此查询在下面的sqlfiddle中实现
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
表的内容
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')
;
查询以提取每个组的最新3条记录.
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
使用MySQL v5.6时的结果如下;
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
MySQL的结果就是我想要的.但是,我使用的是MariaDB,结果与MySQL结果不同.
The MySQL result is what I want. However, I am using MariaDB and the result is different from the MySQL result.
MariaDB结果如下:
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
如何修改查询代码,以便MariaDB的查询输出可以与MySQL相同?在MariaDB中使用窗口函数会是一个好主意吗?
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?
推荐答案
允许查询执行忽略 FROM ( SELECT ... )
中的ORDER BY
.这可能是您看到差异的真正原因. (我认为戈登的答案不相关.)
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.)
在这里(4年前)讨论了这个问题: https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/;通过设置有一个解决方案.
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.
此处还有一些其他解决方案: http://mysql.rjweb.org/doc.php /groupwise_max ;它们旨在提高效率.
Some other solutions are here: http://mysql.rjweb.org/doc.php/groupwise_max ; they are designed to be efficient.
另一种可能解决方案是在子查询中添加数量众多的伪造LIMIT
.
Yet another possible solution is to add a bogus LIMIT
with a large number on the subquery.
这篇关于对于MySQL,每组代码的前N个查询有效,但对于MariaDB,结果不同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!