MySql 对 over 子句使用正确的语法

Question

提问by Nightwolf

What is the correct syntax to get the over clause to work in mysql?

让 over 子句在 mysql 中工作的正确语法是什么？

I would like to see the total sms's sent by each user without grouping it with the group by clause.

我想查看每个用户发送的短信总数，而不用 group by 子句对其进行分组。

SELECT 
    username, 
    count(sentSmsId) OVER (userId) 
FROM 
    sentSmsTable,
    userTable
WHERE
    userId = sentUserId;

Answer 1

There is no OVER clause in MySQL that I know of, but here is a link that might assist you to accomplish the same results:

我知道 MySQL 中没有 OVER 子句，但这里有一个链接可以帮助您完成相同的结果：

Hope this helps.

希望这可以帮助。

Answer 2

MySQL 8 has got the window functions! Therefore, you can write your query in it like this:

MySQL 8 有了窗口函数！因此，您可以像这样在其中编写查询：

SELECT username, 
       count(sentSmsId) OVER (partition by userId) 
FROM sentSmsTable
JOIN userTable ON userId = sentUserId;

Answer 3

MySQL does not currently support window functions, so over()will only yield syntax errors (or garbage, if it's accepted regardless).

MySQL 当前不支持窗口函数，因此over()只会产生语法错误（或垃圾，如果它被接受的话）。

Answer 4

MySQL Doesn't have window functions until the most recent release: MySQL 8 (release in April, 2018). MS SQL Server also accepts OVER clause.

MySQL 在最新版本之前没有窗口函数：MySQL 8（2018 年 4 月发布）。MS SQL Server 也接受 OVER 子句。

The syntax is:

语法是：

function(col1) OVER (PARTITION BY col2 ORDER BY col3)