代码之家 › 专栏 › 技术社区 › qadenza

更新列,使重复的值成为唯一的

mysql php

qadenza · 技术社区 · 6 年前

柱 title 有很多重复的值,不止一次。

我需要更新列,例如 'gold' 是重复的-它变成 'gold 1' , 'gold 2' 等。

像这样的:

$st = $db->query("select id, title from arts order by title asc");
$st->execute();
$x = 0;
while($row = $st->fetch()){
    $title = $row['title'];
    //if($title.is duplicated){
        $x++;
        $title .= ' ' . $x;
        $stb = $db->query("update arts set title = '" . $title . "' where id = " . $row['id']);
        $stb->execute();
    }
}

有什么帮助吗?

1 回复 | 直到 6 年前

GMB 6 年前

用纯sql而不是php来实现这一点会更有效率。这里有一种使用窗口函数的方法,在mysql 8.0中提供。

您可以使用子查询来计算 title 每个记录都存在重复项,并在具有相同记录的记录组中为每个记录分配一个列组 标题 . 那么,你可以 JOIN 要更新的表的子查询。如果存在多个记录,则可以将行号追加到组中的每个记录。

查询:

UPDATE arts a
INNER JOIN (
    SELECT 
        id, 
        title, 
        COUNT(*) OVER(PARTITION BY title) cnt,
        ROW_NUMBER() OVER(PARTITION BY title ORDER BY id) rn
    FROM arts
) b ON a.id = b.id
SET a.title = CONCAT(a.title, b.rn)
WHERE cnt > 1;

Demo on DB Fiddle

样本数据:

| id  | title  |
| --- | ------ |
| 10  | silver |
| 20  | gold   |
| 30  | gold   |
| 40  | bronze |
| 50  | gold   |
| 60  | bronze |

运行更新查询后的结果:

| id  | title   |
| --- | ------- |
| 10  | silver  |
| 20  | gold1   |
| 30  | gold2   |
| 40  | bronze1 |
| 50  | gold3   |
| 60  | bronze2 |

Akbor 6 年前

请看下面对我有用的代码

// Create connection
$conn = new mysqli($servername, $username, $password,$dbname);
// get all row  
$sql = "select id, title from arts order by title asc";
$result = $conn->query($sql);

while ($row=$result->fetch_assoc()) {   
    $title=$row['title'];
    // select where title is same
    $sql = "select * from arts where title='".$title."'";
    $result2 = $conn->query($sql);
    // if number of row is greater then one 
    if ($result2->num_rows > 1){
        $x=0;
        while ($row2=$result2->fetch_assoc()) {
            $id=$row2['id'];
            // skip first row 
            if($x>0){
                $newTitle=$title.' '.$x;
                $uquery = "update arts set title='".$newTitle."' where title='".$title."' and id=$id";                  
                $update = $conn->query($uquery);
            }
            $x++;
        }
    }   
}

在查询运行之后

forpas 6 年前

这在mysql 5.7中有效:

update arts a inner join (
  select * from (
    select t.id,
      (
        select count(*) + 1 from arts 
        where id < t.id and title = t.title 
      ) counter
    from arts t
  ) t 
) t on t.id = a.id
set a.title = concat(a.title, ' ', t.counter)
where a.title in (
  select h.title from (
    select title from arts
    group by title
    having count(*) > 1    
  ) h 
);

见 demo .
对于数据:

| id  | title    |
| --- | -------- |
| 1   | silver   |
| 2   | gold     |
| 3   | diamond  |
| 4   | bronze   |
| 5   | gold     |
| 6   | bronze   |
| 7   | gold     |

结果是

| id  | title    |
| --- | -------- |
| 1   | silver   |
| 2   | gold 1   |
| 3   | diamond  |
| 4   | bronze 1 |
| 5   | gold 2   |
| 6   | bronze 2 |
| 7   | gold 3   |

-1

Chema 6 年前

我认为在sql中这样做会更有效,但是您可以执行一个函数来验证重复项,如下所示:

function isDuplicated( $title, $db ){   
    $dp = $db->query("SELECT * FROM arts WHERE title = $title");        
    if ( $dp->num_rows > 1)
        return true;

    return false;            
}


$st = $db->query("select id, title from arts order by title asc");
$st->execute();
$x = 0;
while($row = $st->fetch()){
    $title = $row['title'];
    if( isDuplicated( $title, $db ) ){
        $x++;
        $title .= ' ' . $x;
        $stb = $db->query("update arts set title = '" . $title . "' where id = " . $row['id']);
        $stb->execute();
    }
}