MySQL转换ROW_NUMBER() OVER PARTITION

Question

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

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

非常感谢您提供的任何帮助。谢谢！

- Mathis Azubike

Answer 1

MySQL将在8.x版本中开始支持窗口函数，例如row_number()（截至2017年10月29日还未生产就绪），而使用@variables是一种模拟效果的技术：

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对于此操作至关重要，如果您需要其他最终排序，请使用上述内容作为子查询。

- Paul Maxwell

---

为什么要使用交叉连接？我知道子查询初始化了那些row_num和prev_value变量，但为什么要用交叉连接呢？ - Code_Jamer

交叉连接只是添加了两个新列。还要注意，由于它只有一行，所以交叉连接不会增加总行数。 - Paul Maxwell

谢谢Paul，非常感谢！ - Code_Jamer

顺便提一下，在许多旧的 MySQL 技术示例中，您可能会发现额外列的子查询仅在 from 子句中列出，但用逗号分隔，并且 where 子句中没有该子查询的连接条件。这具有与显式交叉连接相同的效果，但我不建议混合使用古老的语法和显式连接。在我看来，最好明确地表明正在使用交叉连接。 - Paul Maxwell

非常有道理！ - Code_Jamer