对于另一个列的划分，一种方法是由@abcdn描述的。但是，它的性能很低。我建议使用这段代码，它不需要连接表本身: 考虑同一张桌子。

你可以得到这样的分区:

set @row_num := 0;
set @j:= 0;

select IF(j= @j, @row_num := @row_num + 1, @row_num := 1) as row_num,
       i, @j:= j as j
from tbl fh
order by j, i;

结果是这样的:

这样做的好处是我们不需要连接表本身

重要提示:请考虑升级到MySQL 8+，并使用已定义和文档化的ROW_NUMBER()函数，并抛弃老黑客绑定到一个功能有限的古老版本的MySQL

下面是其中一个小技巧:

这里大部分/全部使用查询变量的答案似乎忽略了一个事实，即文档说(转述):

不要依赖于SELECT列表中的项按从上到下的顺序求值。不要在一个SELECT项中分配变量，而在另一个SELECT项中使用它们

因此，他们有可能会得出错误的答案，因为他们通常会做一个

select
  (row number variable that uses partition variable),
  (assign partition variable)

如果从下向上计算，行号将停止工作(没有分区)

所以我们需要使用一些有执行顺序保证的东西。输入CASE当:

SELECT
  t.*, 
  @r := CASE 
    WHEN col = @prevcol THEN @r + 1 
    WHEN (@prevcol := col) = null THEN null
    ELSE 1 END AS rn
FROM
  t, 
  (SELECT @r := 0, @prevcol := null) x
ORDER BY col

作为大纲ld, prevcol的赋值顺序很重要——在我们从当前行为其赋值之前，必须将prevcol与当前行的值进行比较(否则它将是当前行的col值，而不是前一行的col值)。

以下是它们之间的联系:

The first WHEN is evaluated. If this row's col is the same as the previous row's col then @r is incremented and returned from the CASE. This return led values is stored in @r. It's a feature of MySQL that assignment returns the new value of what is assigned into @r into the result rows. For the first row on the result set, @prevcol is null (it is initialised to null in the subquery) so this predicate is false. This first predicate also returns false every time col changes (current row is different to previous row). This causes the second WHEN to be evaluated. The second WHEN predicate is always false, and it exists purely to assign a new value to @prevcol. Because this row's col is different to the previous row's col (we know this because if it were the same, the first WHEN would have been used), we have to assign the new value to keep it for testing next time. Because the assignment is made and then the result of the assignment is compared with null, and anything equated with null is false, this predicate is always false. But at least evaluating it did its job of keeping the value of col from this row, so it can be evaluated against the next row's col value Because the second WHEN is false, it means in situations where the column we are partitioning by (col) has changed, it is the ELSE that gives a new value for @r, restarting the numbering from 1

我们会遇到这样的情况:

SELECT
  t.*, 
  ROW_NUMBER() OVER(PARTITION BY pcol1, pcol2, ... pcolX ORDER BY ocol1, ocol2, ... ocolX) rn
FROM
  t

具有一般形式:

SELECT
  t.*, 
  @r := CASE 
    WHEN col1 = @pcol1 AND col2 = @pcol2 AND ... AND colX = @pcolX THEN @r + 1 
    WHEN (@pcol1 := pcol1) = null OR (@pcol2 := col2) = null OR ... OR (@pcolX := colX) = null THEN null
    ELSE 1 
  END AS rn
FROM
  t, 
  (SELECT @r := 0, @pcol1 := null, @pcol2 := null, ..., @pcolX := null) x
ORDER BY pcol1, pcol2, ..., pcolX, ocol1, ocol2, ..., ocolX

脚注:

The p in pcol means "partition", the o in ocol means "order" - in the general form I dropped the "prev" from the variable name to reduce visual clutter The brackets around (@pcolX := colX) = null are important. Without them you'll assign null to @pcolX and things stop working It's a compromise that the result set has to be ordered by the partition columns too, for the previous column compare to work out. You can't thus have your rownumber ordered according to one column but your result set ordered to another You might be able to resolve this with subqueries but I believe the docs also state that subquery ordering may be ignored unless LIMIT is used and this could impact performance I haven't delved into it beyond testing that the method works, but if there is a risk that the predicates in the second WHEN will be optimised away (anything compared to null is null/false so why bother running the assignment) and not executed, it also stops. This doesn't seem to happen in my experience but I'll gladly accept comments and propose solution if it could reasonably occur It may be wise to cast the nulls that create @pcolX to the actual types of your columns, in the subquery that creates the @pcolX variables, viz: select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)

对于另一个列的划分，一种方法是由@abcdn描述的。但是，它的性能很低。我建议使用这段代码，它不需要连接表本身: 考虑同一张桌子。

你可以得到这样的分区:

set @row_num := 0;
set @j:= 0;

select IF(j= @j, @row_num := @row_num + 1, @row_num := 1) as row_num,
       i, @j:= j as j
from tbl fh
order by j, i;

结果是这样的:

这样做的好处是我们不需要连接表本身

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

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

我发现最好的解决方案是使用这样的子查询:

SELECT 
    col1, col2, 
    (
        SELECT COUNT(*) 
        FROM Table1
        WHERE col1 = t1.col1
        AND col2 = t1.col2
        AND col3 > t1.col3
    ) AS intRow
FROM Table1 t1

分区BY列只是用'='进行比较，并用and分隔。ORDER BY列将与'<'或'>'进行比较，并以or分隔。

我发现这是非常灵活的，即使它有点昂贵。

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;