MySQL 更新查询以保留重复记录的相同值和新记录的增量值

Question

>我有一个表格，里面已经填充了一些数据。在该表中，我有一个名为 package_code 的列。此列值不是唯一的。有一些重复的值和一些新值等。我还有另一个专栏package_id。我需要根据package_code更新package_id值。例如，package_code是001234，那么package_id是1。如果我们有 10 行带有 001234则package_id为 1。当我们有package_code 004589时，package_id将是 2，依此类推。此更新将基于名为 sim_id 的列进行。下面是我的表格数据。

id package_id package_code sim_id
1             001234       40025
2             001234       40025
3             001234       40025
4             001200       40026
5             001240       40027
6             001240       40027
7             001275       40088
8             001275       40088

我想要的输出将是：

id package_id package_code sim_id
1      1       001234       40025
2      1       001234       40025
3      1       001234       40025
4      2       001200       40026
5      3       001240       40027
6      3       001240       40027
7      4       001275       40088
8      5       001275       40088

我怎样才能做到这一点？任何帮助将不胜感激。这是我尝试使用的查询。

update line_items set package_id = 1 where package_code HAVING count(*)>1 and sim_id = 40025

Answer 1

如果您使用的是更通用的数据库(如 SQL Server(，我们只需在表的 CTE 中分配一个排名，然后直接更新该 CTE。 但是您使用的是MySQL，因此问题变得更加困难。

一种方法是从下表中生成package_id值：

id package_id package_code
1             001234
4             001200
5             001240
7             001275

通过按package_code分组，然后取最小值id值，可以轻松生成此表。 请注意，上表上的以下查询将生成我们想要的package_id值：

SELECT package_code,
    (SELECT COUNT(*) FROM table t2 WHERE t2.id <= t1.id) package_id
FROM table t1;

我们可以使用此逻辑连接到表，然后更新package_id值：

UPDATE yourTable a
INNER JOIN
(
    SELECT
        package_code,
        (SELECT COUNT(*) FROM (SELECT package_code, MIN(id) AS id
                               FROM yourTable
                               GROUP BY package_code) t2
         WHERE t2.id <= t1.id) AS rn
    FROM
    (
        SELECT package_code, MIN(id) AS id
        FROM yourTable
        GROUP BY package_code
    ) t1
) b
    ON a.package_code = b.package_code
SET package_id = b.rn;