sql - 如何使用 SQL 将文本附加到数据库中的重复条目

Question

我如何找到重复项并将随机数附加到重复项，以便它们不再重复。

示例表:

primary_id, student_id, student_name
1           80          John Terry
2           81          Didier Drogba
3           80          John Terry
4           82          Frank Lampard
5           80          John Terry

我想通过在重复项的名称上附加一个随机数来去除重复项。例如。在上面的场景中，我想重命名出现在第 3 行到 112233_DUP_John Terry 和第 5 行到 668877_DUP_John Terry 的学生姓名。请注意，副本的第一个条目保持不变。在这种情况下，第 1 行保持不变。

重命名格式为:6_digit_random_number + _DUP_ + Existing Student Name

到目前为止，我可以使用以下 SQL 获取重复项:

SELECT student_id, student_name FROM (select student_id, student_name, count(*) from student
          group by student_id, student_name
          HAVING count(*) > 1 order by count DESC) AS duplicates

我知道我也可以使用 SQL 生成一个随机数，但我不知道如何将它附加到重复条目

正在运行 Postgresql 数据库

Answer 1

首先使用窗口函数而不是您的组方法获取重复的行，例如

SELECT
  primary_id, student_id, student_name
FROM 
(
  SELECT
    row_number() OVER (PARTITION BY student_id, student_name) AS dup_no,
    primary_id, student_id, student_name
  FROM students
) dup
WHERE dup.dup_no > 1; 

然后将其与 UPDATE ... FROM 结合以仅更新重复项:

UPDATE students
SET student_name = to_char(dupstudents.dup_no, '000000') || '_DUP_' || students.student_name
FROM (
  SELECT
    row_number() OVER (PARTITION BY student_id, student_name) AS dup_no,
    primary_id, student_id, student_name
  FROM students
) dupstudents
WHERE students.primary_id = dupstudents.primary_id
  AND dupstudents.dup_no > 1;

例如http://sqlfiddle.com/#!15/5b1b8/9

我没有理会“随机 ID”位；我只是使用了重复的偏移位置。随意将其替换为对 (random()*10^6)::integer 或其他任何内容的适当调用，但要注意随机值冲突。