在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最高的单行。
当前回答
如果查询有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
其他回答
如果查询有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个字段进行了测试。希望这能有所帮助。查看这篇文章,因为它分解了这个查询并解释了它。
从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演示
MySQL中没有排名功能。最接近的方法是使用变量:
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
那么在我的案例中这是如何起作用的呢?我需要两个变量,分别代表col1和col2?当col1改变时,Col2需要以某种方式重置…?
是的。如果它是Oracle,您可以使用LEAD函数在下一个值处达到峰值。值得庆幸的是,Quassnoi涵盖了在MySQL中需要实现的逻辑。
当我们有一个以上的列时,这个工作完美地为我创建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