mysql中有没有一种很好的方法来复制sqlserver函数 ROW_NUMBER()
?
例如:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
例如,我可以添加一个限制条件 intRow
到1以获得具有最高 col3
对于每个 (col1, col2)
一对。
mysql中有没有一种很好的方法来复制sqlserver函数 ROW_NUMBER()
?
例如:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
例如,我可以添加一个限制条件 intRow
到1以获得具有最高 col3
对于每个 (col1, col2)
一对。
20条答案
按热度按时间jfewjypa1#
重要提示:请考虑升级到mysql 8+,并使用已定义并有文档记录的row_number()函数,摒弃与功能有限的mysql旧版本相关的旧方法
下面是其中一个黑客:
这里使用查询变量的答案大部分/全部似乎忽略了一个事实,即文档中说(意译):
不要依赖于从上到下依次计算的选择列表中的项目。不要在一个选择项中分配变量,而在另一个选择项中使用它们
因此,他们很有可能会给出错误的答案,因为他们通常会做出错误的回答
如果自下而上计算这些值,行号将停止工作(没有分区)
所以我们需要使用一些有保证的执行顺序的东西。输入case时:
正如outline ld一样,prevcol的赋值顺序很重要-在我们从当前行中为其赋值之前,必须将prevcol与当前行的值进行比较(否则它将是当前行的col值,而不是前一行的col值)。
以下是这两者的结合方式:
第一次评估时。如果此行的列与前一行的列相同,则@r将递增并从case返回。此返回led值存储在@r中。mysql的一个特性是赋值将@r中赋值的新值返回到结果行中。
对于结果集的第一行,@prevcol为null(在子查询中初始化为null),因此该 predicate 为false。每次列更改时,第一个 predicate 也会返回false(当前行与前一行不同)。这将导致第二次评估。
第二个 predicate 总是false,它的存在纯粹是为了给@prevcol赋值。因为这一行的col与前一行的col不同(我们知道这一点是因为如果它是相同的,第一次使用它的时候),所以我们必须分配新的值,以便下次测试时保留它。因为赋值是进行的,然后赋值的结果与null进行比较,并且任何等同于null的东西都是false,所以这个 predicate 总是false。但至少评估它的工作是保留此行的col值,因此可以根据下一行的col值对其进行评估
因为第二个when为false,这意味着在我们划分的列(col)发生了变化的情况下,else为@r提供了一个新的值,从1开始重新编号
我们的情况是:
具有一般形式:
脚注:
pcol中的p表示“partition”,ocol中的o表示“order”——在一般形式中,我从变量名中去掉了“prev”,以减少视觉混乱
周围的支架
(@pcolX := colX) = null
是很重要的。如果没有它们,你会给@pcolx赋值null,事情就停止了这是一个折衷方案,结果集也必须按分区列排序,以便前一列比较得到结果。因此,您不能根据一列对行数进行排序,但结果集会被排序到另一列。您可能可以通过子查询解决此问题,但我相信文档中还指出,除非使用限制,否则子查询排序可能会被忽略,这可能会影响性能
除了测试方法是否有效之外,我还没有深入研究它,但是如果第二个方法中的 predicate 有可能被优化掉(与null相比的任何东西都是null/false,那么为什么要费心运行赋值)而没有执行,那么它也会停止。根据我的经验,这似乎没有发生过,但如果可能的话,我很乐意接受意见并提出解决方案
在创建@pcolx变量的子查询中,将创建@pcolx的null强制转换为列的实际类型可能是明智的,即:
select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)
55ooxyrt2#
有点晚,但也可能有助于寻找答案的人。。。
行间/行号示例-可在任何sql中使用的递归查询:
3pvhb19x3#
我也会投票支持mosty mostacho的解决方案,只是对他的查询代码做了一些小的修改:
结果是一样的:
对于表格:
唯一的区别是查询不使用join和groupby,而是依赖嵌套的select。
fkaflof64#
我总是遵循这种模式。给出下表:
您可以得到以下结果:
通过运行不需要定义任何变量的查询:
希望有帮助!
093gszye5#
mysql中的行数查询
xggvc2p66#
mysql从版本8.0+开始就支持行\ u number()。
如果您使用MySQL8.0或更高版本,请检查row_number()函数。否则,您将使用simulate row\ u number()函数。
row\ u number()是一个排名函数,返回一行的序列号,第一行从1开始。
对于旧版本,
bksxznpy7#
没有比这更好的功能了
rownum
,row_num()
在mysql中,但方法如下:toe950278#
看看这篇文章,它展示了如何在mysql中用一个partition by来模拟sql row_number()。我在wordpress实现中遇到了同样的情况。我需要第()行,但它不在那里。
http://www.explodybits.com/2011/11/mysql-row-number/
本文中的示例是使用按字段的单个分区。要按其他字段进行分区,可以执行以下操作:
使用concat\u ws处理null。我使用int、date和varchar对3个字段进行了测试。希望这有帮助。查看文章,因为它分解了这个查询并解释了它。
kkbh8khc9#
我看不到任何简单的答案涵盖“分区方式”部分,所以我的答案是:
ORDERBY子句必须反映您的行号需求。因此,已经有了一个明确的限制:不能同时有多个行号“仿真”这个表单。
“计算列”的顺序很重要。如果您让mysql以另一种顺序计算这些列,它可能无法工作。
在这个简单的例子中,我只放了一个,但是您可以有几个“partitionby”部分
yduiuuwa10#
mariadb 10.2正在实现“窗口函数”,包括rank()、row\u number()和其他一些东西:
https://mariadb.com/kb/en/mariadb/window-functions/
根据本月在percona live上的一次演讲,它们得到了合理的优化。
语法与问题中的代码相同。
6rqinv9w11#
mysql中没有排名功能。最接近的方法是使用变量:
那么在我的情况下这是怎么回事呢?我需要两个变量,col1和col2各一个?当col1发生变化时,col2需要重新设置。。?
对。如果是oracle,可以使用lead函数在下一个值处达到峰值。谢天谢地,quassnoi涵盖了您需要在mysql中实现的逻辑。
qlckcl4x12#
从
MySQL 8.0.0
在上面你可以使用窗口函数。1.4 mysql 8.0的新增功能:
窗口功能。
mysql现在支持窗口函数,对于查询中的每一行,使用与该行相关的行执行计算。这些函数包括rank()、lag()和ntile()等函数。另外,一些现有的聚合函数现在可以用作窗口函数;例如,sum()和avg()。
子句上的行数():
返回分区中当前行的编号。行数的范围从1到分区行数。
order by影响行的编号顺序。没有order by,行号是不确定的。
演示:
dbfiddle演示
bihw5rsg13#
也有点晚,但今天我有同样的需要,所以我在谷歌上搜索,最后一个简单的一般方法在这里找到皮纳尔戴夫的文章http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/
我想把重点放在保罗最初的问题上(那也是我的问题),所以我总结了我的解决方案作为一个工作示例。
因为我们要在两列上划分,所以我会创建一个变量集
xkrw2x1b14#
这也可以是一个解决方案:
9udxz4iz15#