在MySQL中有一个很好的方法来复制SQL Server函数ROW_NUMBER()?
例如:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
然后,例如,我可以添加一个条件,将intRow限制为1,以获得每个(col1, col2)对的col3最高的单行。
在MySQL中有一个很好的方法来复制SQL Server函数ROW_NUMBER()?
例如:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
然后,例如,我可以添加一个条件,将intRow限制为1,以获得每个(col1, col2)对的col3最高的单行。
当前回答
MySQL从8.0+版本开始支持ROW_NUMBER()。
如果使用MySQL 8.0或更高版本,请检查ROW_NUMBER()函数。 否则,您将使用模拟ROW_NUMBER()函数。
row_number()是一个排序函数,返回一行的顺序编号,第一行从1开始。
对于旧版本,
SELECT t.*,
@rowid := @rowid + 1 AS ROWID
FROM TABLE t,
(SELECT @rowid := 0) dummy;
其他回答
我想要每对(col1, col2)的col3最高的行。
这是一个分组最大值,是最常被问到的SQL问题之一(因为它看起来应该很简单,但实际上并非如此)。
我经常支持null-self-join:
SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;
"获取表中没有匹配col1、col2的其他行具有更高的col3的行。(你会注意到,如果不止一行具有相同的col1、col2、col3,那么这个和大多数其他分组最大值解将返回多行。如果这是一个问题,你可能需要一些后期处理。)
我也会投票给Mosty Mostacho的解决方案,对他的查询代码进行了轻微的修改:
SELECT a.i, a.j, (
SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a
会得到相同的结果:
+------+------+------------+
| i | j | row_number |
+------+------+------------+
| 1 | 11 | 1 |
| 1 | 12 | 2 |
| 1 | 13 | 3 |
| 2 | 21 | 1 |
| 2 | 22 | 2 |
| 2 | 23 | 3 |
| 3 | 31 | 1 |
| 3 | 32 | 2 |
| 3 | 33 | 3 |
| 4 | 14 | 1 |
+------+------+------------+
对于表格:
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
唯一的区别是查询不使用JOIN和GROUP BY,而是依赖于嵌套选择。
看看这篇文章,它展示了如何在MySQL中使用分区by模拟SQL ROW_NUMBER()。我在WordPress实现中遇到了同样的场景。我需要ROW_NUMBER(),但它不在那里。
http://www.explodybits.com/2011/11/mysql-row-number/
本文中的示例使用了一个按字段划分的分区。要按额外的字段进行分区,你可以这样做:
SELECT @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
,t.col1
,t.col2
,t.Col3
,t.col4
,@prev_value := concat_ws('',t.col1,t.col2)
FROM table1 t,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY t.col1,t.col2,t.col3,t.col4
使用concat_ws处理null。我使用int、date和varchar对3个字段进行了测试。希望这能有所帮助。查看这篇文章,因为它分解了这个查询并解释了它。
在MySQL中没有像rownum, row_num()这样的函数,但方法如下:
select
@s:=@s+1 serial_no,
tbl.*
from my_table tbl, (select @s:=0) as s;
从MySQL 8.0.0及以上版本,您可以本机使用窗口函数。
1.4 MySQL 8.0的新特性:
窗口函数。 MySQL现在支持窗口函数,对于查询中的每一行,使用与该行相关的行执行计算。这些函数包括RANK()、LAG()和NTILE()。此外,一些现有的聚合函数现在可以用作窗口函数;例如SUM()和AVG()。
ROW_NUMBER() over_clause:
返回分区内当前行的编号。行号从1到分区行数。 ORDER BY影响行编号的顺序。没有ORDER BY,行编号是不确定的。
演示:
CREATE TABLE Table1(
id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);
INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
(2,1,'x'),(2,1,'y'),(2,2,'z');
SELECT
col1, col2,col3,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1;
DBFiddle演示