MSSQL到MySQL(5.7)行号

Question

我正在将我的数据库从MSSQL迁移到MySQL。在版本8之前，MySQL不支持row_number。我应该如何为下面的查询进行迁移。

SELECT
  A.Att1,
  B.Att2,
  ...
  SeqNr = ROW_NUMBER() OVER (PARTITION BY A.ID, B.AnotherID ORDER BY C.SomeAttribute DESC),
FROM TableOne A
INNER JOIN TableTwo B
  ON A.ID = B.ID
INNER JOIN TableThree C
  ON B.ID = C.ID

row_number()OVER（partition...order BY)部分应该可以在MySQL8+中开箱即用，但我目前使用的是5.7，最好的替代方案是什么？

多谢了。

Answer 1

使用变量：

SELECT abc.*,
       (@rn := IF(CONCAT_WS(':', A.id, B.AnotherID) = @ii, @rn + 1,
                  IF(@ii := CONCAT_WS(':', A.id, B.AnotherID), 1, 1)
                 )
       ) as seqnum
FROM (SELECT . . .
      FROM TableOne A JOIN
           TableTwo B
           ON A.ID = B.ID JOIN
           TableThree C
           ON B.ID = C.ID
      ORDER BY A.id, B.AnotherID, C.SomeAttribute DESC
     ) abc CROSS JOIN
     (SELECT @ii := '', @rn := 0) params;