Mysql:使用选择和局部变量更新

Question

我正在寻找一份更新声明，该声明将在下表中按语言对术语进行分组

CREATE TABLE _tempTerms(
      ID int(8) unsigned NOT NULL AUTO_INCREMENT,
      TTC_ART_ID mediumint(8) unsigned,
      TTC_TYP_ID mediumint(8) unsigned,
      Name varchar(200),
      Value varchar(200),
      ID_Lang tinyint(3) unsigned,
      Sequence smallint unsigned,
      Group_ID int(8) unsigned DEFAULT 0,
      PRIMARY KEY(TTC_ART_ID, TTC_TYP_ID, Name, Value),
      UNIQUE KEY(ID)
      );

除 Group_ID 之外的所有数据都将插入到表中。我需要更新表，以便自动生成新的 Group_ID，并且具有相同 TTC_ART_ID、TTC_TYP_ID 和序列组合的所有记录的 Group_ID 将获得相同的 Group_ID。我想我需要一个变量来存储 Group_ID 的当前值，到目前为止我已经尝试过

SET @group_id:=1;
UPDATE _tempTerms 
SET Group_ID = (@group_id := @group_id + 1);

这只是为每个新记录提供一个新的 group_id 。我相信我需要在某处使用 SELECT 语句来检查是否已经给出了 group_id，但我对如何进行操作感到困惑。

谢谢

Answer 1

架构:

create database xGrpId; -- create a test db
use xGrpId; -- use it

CREATE TABLE _tempTerms(
      ID int(8) unsigned NOT NULL AUTO_INCREMENT,
      TTC_ART_ID mediumint(8) unsigned,
      TTC_TYP_ID mediumint(8) unsigned,
      Name varchar(200),
      Value varchar(200),
      ID_Lang tinyint(3) unsigned,
      Sequence smallint unsigned,
      Group_ID int(8) unsigned DEFAULT 0,
      PRIMARY KEY(TTC_ART_ID, TTC_TYP_ID, Name, Value),
      UNIQUE KEY(ID)
      );

-- truncate table _tempTerms;
insert _tempTerms(TTC_ART_ID,TTC_TYP_ID,Name,Value,ID_Lang,Sequence) values
(1,2,'n','v1',66,4),
(1,1,'n','v2',66,4),
(1,1,'n','v3',66,3),
(1,1,'n','v4',66,4),
(1,1,'n','v5',66,4),
(1,1,'n','v6',66,3),
(2,1,'n','v7',66,4),
(1,2,'n','v8',66,4);

查看它们:

select * from _tempTerms order by id;
select distinct TTC_ART_ID,TTC_TYP_ID,Sequence from _tempTerms; 
-- 4 rows

-- 更新 _tempTerms 设置 Group_ID=0； -- 测试前先明确

查询:

update _tempTerms t
join
(   select TTC_ART_ID,TTC_TYP_ID,Sequence,@rn:=@rn+1 as rownum
    from
    (   select distinct TTC_ART_ID,TTC_TYP_ID,Sequence
        from _tempTerms
        -- put your `order by` here if needed
        order by TTC_ART_ID,TTC_TYP_ID,Sequence
    ) d1
    cross join (select @rn:=0) as xParams
) d2
on d2.TTC_ART_ID=t.TTC_ART_ID and d2.TTC_TYP_ID=t.TTC_TYP_ID and d2.Sequence=t.Sequence
set t.Group_ID=d2.rownum;

结果:

select * from _tempTerms order by TTC_ART_ID,TTC_TYP_ID,Sequence;

+----+------------+------------+------+-------+---------+----------+----------+
| ID | TTC_ART_ID | TTC_TYP_ID | Name | Value | ID_Lang | Sequence | Group_ID |
+----+------------+------------+------+-------+---------+----------+----------+
|  3 |          1 |          1 | n    | v3    |      66 |        3 |        1 |
|  6 |          1 |          1 | n    | v6    |      66 |        3 |        1 |

|  2 |          1 |          1 | n    | v2    |      66 |        4 |        2 |
|  4 |          1 |          1 | n    | v4    |      66 |        4 |        2 |
|  5 |          1 |          1 | n    | v5    |      66 |        4 |        2 |

|  1 |          1 |          2 | n    | v1    |      66 |        4 |        3 |
|  8 |          1 |          2 | n    | v8    |      66 |        4 |        3 |

|  7 |          2 |          1 | n    | v7    |      66 |        4 |        4 |
+----+------------+------------+------+-------+---------+----------+----------+

清理:

drop database xGrpId;

d1、d2 和 xParams 是派生表。每个派生表都需要一个名称。 xParams 和cross join 的目的仅仅是引入一个变量来初始化行号。这是因为 mysql 缺乏其他 RDBMS 中的 CTE 功能。因此，不要过度考虑交叉连接。这就像说LET i=0。