在 MariaDB 中使用 ROW_NUMBER() 函数的问题 [英] Problem Using ROW_NUMBER() function in MariaDB

问题描述

我想在选择表输出中有一个行号列，但是当我尝试使用 ROW_NUMBER() 函数时，MariaDB 会引发语法错误.网上有几个参考资料(http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/ )但到目前为止我还没有成功.这是我的 MariaDB 表的一部分:

I would like to have a row number column in a select table output, but when I try using the ROW_NUMBER() function MariaDB throws a syntax error. There are several references on the web (http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/ ) but so far I have not been successful. Here is a segment of my MariaDB table:

+---------------------+------------+  
| date_reading        | temp_patio |
|---------------------+------------+  
| 2019-09-03 06:26:00 |       17.6 |  
| 2019-09-03 06:33:00 |       17.5 |  
| 2019-09-03 06:40:00 |       17.5 |  
| 2019-09-03 06:46:00 |       17.5 |  
| 2019-09-03 06:53:00 |       17.4 |  
| 2019-09-03 07:00:00 |       17.4 |  
| 2019-09-03 07:07:00 |       17.4 |  
| 2019-09-03 07:13:00 |       17.4 |

文档说OVER ()" 选项的选项是可选的，但我已经尝试过使用和不使用 OVER() 子句以及使用和没有 ORDER BY 子句.

The document says that the options for the "OVER ()" option are optional, but I have tried both with and without an OVER () clause and with and without an ORDER BY clause.

这是我的选择命令:

select ROW_NUMBER() OVER ( ) as Therow, * from MyData whereDate_Reading > Now()- INTERVAL 3 HOUR;

select ROW_NUMBER() OVER ( ) as Therow, * from MyData where Date_Reading > Now()- INTERVAL 3 HOUR;

可选地，我尝试不使用 OVER () 子句，也尝试使用 OVER ( ORDER BY ID).

Optionally I have tried without the OVER () clause and also using OVER ( ORDER BY ID).

我的 MariaDB 版本是

My MariaDB version is

服务器版本:10.1.38-MariaDB-0+deb9u1 Raspbian 9.0

Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0

有人可以帮忙吗?...RDK

Can someone assist?...RDK

推荐答案

窗口函数 仅在 MariaDB 10.2 或更高版本中受支持.

Window functions are supported in MariaDB 10.2 or higher version only.

MariaDB 10.2 或更高版本:

MariaDB 10.2 or higher:

SELECT 
    MyData.*,
    ROW_NUMBER() OVER ( ORDER BY ID ) as Therow
FROM MyData 
WHERE Date_Reading > Now()- INTERVAL 3 HOUR;

对于较低版本:

我们可以使用MySQL变量来做这份工作.

We can use the MySQL variable to do this job.

SELECT 
    MyData.*, 
    @row_num:= @row_num + 1 AS Therow
FROM 
    MyData, 
    (SELECT @row_num:= 0 AS num) AS c
WHERE Date_Reading > Now()- INTERVAL 3 HOUR
ORDER BY test.`date` ASC;

这篇关于在 MariaDB 中使用 ROW_NUMBER() 函数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！