在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最高的单行。
当前回答
当我们有一个以上的列时,这个工作完美地为我创建RowNumber。这里是两列。
SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber,
`Fk_Business_Unit_Code`,
`NetIQ_Job_Code`,
`Supervisor_Name`,
@prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`
FROM Employee
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC
其他回答
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;
我总是遵循这个模式。给定这个表格:
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
你可以得到这样的结果:
+------+------+------------+
| 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 |
+------+------+------------+
通过运行这个不需要定义任何变量的查询:
SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j
我也会投票给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,而是依赖于嵌套选择。
我没有看到任何简单的答案涵盖“PARTITION BY”部分,所以我的答案是:
SELECT
*
FROM (
select
CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
, @partitionBy_1:=l AS p
, t.*
from (
select @row_number:=0,@partitionBy_1:=null
) as x
cross join (
select 1 as n, 'a' as l
union all
select 1 as n, 'b' as l
union all
select 2 as n, 'b' as l
union all
select 2 as n, 'a' as l
union all
select 3 as n, 'a' as l
union all
select 3 as n, 'b' as l
) as t
ORDER BY l, n
) AS X
where i > 1
The ORDER BY clause must reflect your ROW_NUMBER need. Thus there's already a clear limitation: you can't have several ROW_NUMBER "emulation" of this form at the same time. The order of the "computed column" matters. If you have mysql compute those column in another order, it might not work. In this simple example I only put one but you can have several "PARTITION BY" parts CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i , @partitionBy_1:=part1 AS P1 , @partitionBy_2:=part2 AS P2 [...] FROM ( SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...] ) as x
有点晚了,但也可能对那些寻找答案的人有帮助……
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