在 MariaDB 中使用 ROW_NUMBER() 函数的问题

本文介绍了在 MariaDB 中使用 ROW_NUMBER() 函数的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想在选择表输出中有一个行号列，但是当我尝试使用 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;

可选地，我尝试不使用 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

有人可以帮忙吗?...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() 函数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！