在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;

其他回答

有点晚了,但也可能对那些寻找答案的人有帮助……

Between rows/row_number示例-可以在任何SQL中使用的递归查询:

WITH data(row_num, some_val) AS 
(
 SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
  UNION ALL
 SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number
)
SELECT * FROM data
 WHERE row_num BETWEEN 5 AND 10
/

ROW_NUM    SOME_VAL
-------------------
5           11
6           16
7           22
8           29
9           37
10          46

如果查询有GROUP BY语句,使用交叉连接和逗号的解决方案将不起作用。对于这种情况,您可以使用subselect:

SELECT (@row_number := @row_number + 1) AS rowNumber, res.*
FROM
(
  SELECT SUM(r.amount) 
  FROM Results r 
  WHERE username = 1 
  GROUP BY r.amount
) res
CROSS JOIN (SELECT @row_number := 0) AS dummy

看看这篇文章,它展示了如何在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个字段进行了测试。希望这能有所帮助。查看这篇文章,因为它分解了这个查询并解释了它。

我也会投票给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文档说的:

对于其他语句,例如SELECT,您可能会得到您期望的结果,但这并不保证。在下面的语句中,你可能认为MySQL会先计算@a,然后再赋值: SELECT @a, @a:=@a+1,… 但是,涉及用户变量的表达式的求值顺序是未定义的。

问候, 格奥尔基。