安排重复项并按顺序对记录编号-MySQL

问题描述

我的MySQL表具有以下记录，

My MySQL table has below records,

    ID Name   Account
-----------------------------------------
    1  ABC    PQR
    2  DEF    PQR
    3  ABC    PQR
    4  XYZ    ABC
    5  DEF    PQR
    6  DEF    ABC

我正在寻找类似

    ID Name   Account   Duplicate Sr 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

在这里，我的意思是每个重复项应具有一个Sr编号或为重复项编号.

Here i mean that each duplicate should have a Sr Number or number the duplicates.

名称:ABC和帐户:PQR当在表中重复时，重复Sr No从1递增到2

Name : ABC and Account : PQR when repeated in the table, there is an increment in the Duplicate Sr No from 1 to 2

推荐答案

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

• SQLFiddle演示

输出

╔════╦══════╦═════════╦════════════════╗
║ 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 ║
╚════╩══════╩═════════╩════════════════╝

这篇关于安排重复项并按顺序对记录编号-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！