MySQL将重复单词的数量插入列 - MySQL insert number of repeated word to column

MySQL insert number of repeated word to column

本文关键字：插入列单词 MySQL | 更新日期: 2023-09-27

我有一个表格，里面有重复的单词和一个以"num"命名的空字段。

+----+--------------+-----+
| id | words        | num |
+----+--------------+-----+
| 1  | red          |     |
+----+--------------+-----+
| 2  | blue         |     |
+----+--------------+-----+
| 3  | red          |     |
+----+--------------+-----+
| 4  | red          |     |
+----+--------------+-----+
| 5  | blue         |     |
+----+--------------+-----+

我想在数字字段中插入重复单词的数量，如下所示：

+----+--------------+-----+
| id | words        | num |
+----+--------------+-----+
| 1  | red          | 1   |
+----+--------------+-----+
| 2  | blue         | 1   |
+----+--------------+-----+
| 3  | red          | 2   |
+----+--------------+-----+
| 4  | red          | 3   |
+----+--------------+-----+
| 5  | blue         | 2   |
+----+--------------+-----+

我怎样才能通过PHP或SQL来做到这一点？

这是

给MySQL的。

您可以使用相关子查询联接一个子查询，该子查询根据ID在每个words上生成行号。

UPDATE  tableName a
        INNER JOIN
        (
            SELECT  a.id, 
                    (
                        SELECT  COUNT(*)
                        FROM    tableName c
                        WHERE   c.words = a.words AND
                                c.id <= a.id
                    ) AS RowNumber
            FROM    TableName a
        ) b ON a.id = b.id
SET     a.num = b.rownumber

SQLFiddle 演示

这样的事情将是一种简单的方法来实现它：

CREATE TABLE tmp_colors ( 
   id int auto_increment primary key,
   word varchar(16) NOT NULL
);
INSERT INTO tmp_colors SELECT words FROM table GROUP BY words;
UPDATE table t1 JOIN tmp_colors t2 ON t2.word = t1.words SET t1.num = t2.id;

如果 DBMS 支持窗口函数（如 Oracle 或 MS SQL Server）：

select id,word,row_number() over(partition by word order by id)
from t1 
order by id;