MySQL 转换 ROW_NUMBER() OVER PARTITION [英] MySQL Convert ROW_NUMBER() OVER PARTITION

问题描述

我正在尝试将 MSSQL 数据库转换为 MySQL，我的版本是 5.7.我遇到了障碍.

I'm trying to convert an MSSQL database to MySQL and my version is 5.7. I have hit a roadblock.

SELECT orderid, invs.[InvoiceID],  invs.[InvoiceDate],
invs.[InvoiceNumber], invs.[HasClientPaid],
ROW_NUMBER() OVER (PARTITION by orderid,invs.invoicenumber,HasClientpaid ORDER BY orderid) AS DistNum 
FROM InvoiceLineItems Ilt 
JOIN Invoices Invs ON Ilt.InvoiceID= invs.InvoiceID

任何帮助将不胜感激.谢谢

Any help would be greatly appreciated. Thank you

推荐答案

MySQL 将在 8.x 版中开始支持诸如 row_number() 之类的窗口函数(尚未生产就绪，因为在 2017 年 10 月 29 日)，在那之前使用@variables 是一种模仿效果的技术:

MySQL will commence to suport window functions such as row_number() in version 8.x (not yet production ready as at Oct 29 2017), until then using @variables is a technique to mimic the effect:

SELECT
      @row_num :=IF(@prev_value=concat_ws('',orderid, invs.invoicenumber, HasClientpaid),@row_num+1,1)AS RowNumber
    , orderid
    , invs.[InvoiceID]
    , invs.[InvoiceDate]
    , invs.[InvoiceNumber]
    , invs.[HasClientPaid]
    , @prev_value := concat_ws('',orderid, invs.invoicenumber, HasClientpaid)
FROM InvoiceLineItems Ilt
JOIN Invoices Invs ON Ilt.InvoiceID = invs.InvoiceID
CROSS JOIN (SELECT @row_num :=1,  @prev_value :=0) vars
ORDER BY
      orderid, invs.invoicenumber, HasClientpaid
;

您需要连接 3 个字段 orderid、invs.invoicenumber、HasClientpaid 以模仿您的原始分区，并且排序也需要按这 3 列进行.ORDER BY 对于此工作至关重要，如果您需要其他一些最终排序，请使用上述作为子查询.

You need to concatenate the 3 fields orderid, invs.invoicenumber, HasClientpaid to mimic your original partitioning, and the ordering will need to be by those 3 columns as well. The ORDER BY is essential for this to work, if you need some other final ordering use the above as a subquery.

这篇关于MySQL 转换 ROW_NUMBER() OVER PARTITION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！