更新时间:2023-02-16 13:39:55
MySQL尚未像其他 RDBMS
一样支持 Window Function
.此行为与 ROW_NUMBER()
类似,该行为为组中的每个记录提供等级编号.在mysql中,可以使用用户变量进行仿真.
MySQL doesn't yet support Window Function
like any other RDBMS
. This behaviour is similar with ROW_NUMBER()
which gives rank number for every record in a group. In mysql, this can be simulated by using user variables.
SELECT ID, Name, Account, DuplicateSR_No
FROM
(
select ID,
Name,
Account,
@sum := if(@nme = Name AND @acct = Account, @sum ,0) + 1 DuplicateSR_No,
@nme := Name,
@acct := Account
from TableName,
(select @nme := '', @sum := 0, @acct := '') vars
order by Name, Account
) s
ORDER BY ID
输出
╔════╦══════╦═════════╦════════════════╗
║ ID ║ NAME ║ ACCOUNT ║ DUPLICATESR_NO ║
╠════╬══════╬═════════╬════════════════╣
║ 1 ║ ABC ║ PQR ║ 1 ║
║ 2 ║ DEF ║ PQR ║ 1 ║
║ 3 ║ ABC ║ PQR ║ 2 ║
║ 4 ║ XYZ ║ ABC ║ 1 ║
║ 5 ║ DEF ║ PQR ║ 2 ║
║ 6 ║ DEF ║ ABC ║ 1 ║
╚════╩══════╩═════════╩════════════════╝