The rating table has a record ID and a user ID. How can the duplicate user scores be collapsed by setting the value of rank equal to the sum of all his grades to write?

CREATE TABLE Rank ( `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `postId` INT(11) NOT NULL, `userId` INT(11) NOT NULL, `rank` INT(11) NOT NULL, ); |------|--------|--------|------| | id | postId | userId | rank | |------|--------|--------|------| | 1 | 1 | 1 | 5 | | 2 | 1 | 2 | 5 | | 3 | 1 | 1 | 4 | | 4 | 2 | 2 | 5 | | 5 | 2 | 1 | 2 | | 6 | 3 | 1 | 3 | | 7 | 3 | 1 | 4 | |------|--------|--------|------|

After transformation:

 |------|--------|--------|------| | id | postId | userId | rank | |------|--------|--------|------| | 1 | 1 | 1 | 9 | | 2 | 1 | 2 | 5 | | 4 | 2 | 2 | 5 | | 5 | 2 | 1 | 2 | | 6 | 3 | 1 | 7 | |------|--------|--------|------|

    1 answer 1

     select min(id) as id, postId, userId, sum(rank) as rank from Rank group by postId, userId

    In order to change the data in the table, based on these conditions, two requests must be fulfilled, first changing the Rank in the "first" records:

     update Rank A inner join (select min(id) as id,userId, postId,sum(rank) as rank from Rank group by userId, postId) B on A.id=B.id set A.rank=B.rank

    Then remove the extra entries:

     delete D.* from Rank D inner join( select id from Rank A where exists(select 1 from Rank B where B.postId=A.postId and B.userId=A.userId and B.id<A.id) ) B on D.id=B.id
    • Thank you, but this query does not remove duplicate lines. He just does not show them because of the group. I need to remove them - atom-22