在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最高的单行。
当前回答
我总是遵循这个模式。给定这个表格:
+------+------+
| 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
其他回答
MySQL中没有排名功能。最接近的方法是使用变量:
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
那么在我的案例中这是如何起作用的呢?我需要两个变量,分别代表col1和col2?当col1改变时,Col2需要以某种方式重置…?
是的。如果它是Oracle,您可以使用LEAD函数在下一个值处达到峰值。值得庆幸的是,Quassnoi涵盖了在MySQL中需要实现的逻辑。
这也可以是一个解决方案:
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees
对于另一个列的划分,一种方法是由@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;
结果是这样的:
这样做的好处是我们不需要连接表本身
MariaDB 10.2实现了“窗口函数”,包括RANK(), ROW_NUMBER()和其他一些东西:
https://mariadb.com/kb/en/mariadb/window-functions/
根据本月在Percona Live上的一次演讲,它们得到了合理的优化。
语法与问题中的代码相同。
我想要每对(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,那么这个和大多数其他分组最大值解将返回多行。如果这是一个问题,你可能需要一些后期处理。)