mysql - 根据三列介绍4位数字

我有一个有趣的问题，我想为一个表生成一个8位唯一ID。我有一个列中的前4位数字，任务是将另外四位数字附加到现有的4位数字。以下是条件和样本数据。
前四位数字在下面示例数据的“AFFECTSID_NEW”列中找到
样本数据：

AFFECTSID_NEW | activityname​                | actionname
---------------------------------------------------------------
2301          | Default Proposed Activity   | sample 2
2301          | Communicating welcome pack  | sample 1
1206          | Execute events (7 Events)   | International Trade Seminar
1206          | Execute events (8 Events)   | Workshop with one law firm
1206          | Execute events (8 Events)   | Workshop with International Speaker
1206          | Execute events (8 Events)   | Seminar with Government agency
1206          | Execute events (8 Events)   | Execute events (8 Events)
1205          | Resolve commercial disputes | Resolve commercial disputes

案例：
用于生成第5位和第6位数字
对于相同的“Affectsid_new”和唯一的“activityname”，应该是01、02、03。。。
对于非唯一的“activityname”和“activityname”=“actionname”，如果“Affectsid_new”也是非唯一的，则应该是上述第1点的连续数字；如果“Affectsid_new”是唯一的，则应该是“01”
​
一旦生成第5位和第6位，这些就是生成第7位和第8位的条件
对于相同的“Affectsid_new”和唯一的“activityname”
是“01”
对于非唯一的“activityname”和“activityname”=“actionname”，应为“01”
对于非唯一的“activityname”和“activityname”≠“actionname”（不相等），如果“Affectsid_new”也是非唯一的，则它应该是上述点2（02,03,04..）的连续数字；如果“Affectsid_new”是唯一的，则它应该是“01”
预期结果如下

AFFECTSID_NEW | activityname​                   | actionname                           | Expected
---------------------------------------------------------------------------------------------------
2301          | Default Proposed Activity      | sample 2                             | 23010101
2301          | Communicating welcome pack     | sample 1                             | ​23010201
1206          | Execute events (7 Events)      | International Trade Seminar          | 12060101
1206          | Execute events (8 Events)      | Workshop with one law firm           | ​12060202
1206          | Execute events (8 Events)      | Workshop with International Speaker  | ​12060203
1206          | Execute events (8 Events)      | Seminar with Government agency       | ​12060204
1206          | Execute events (8 Events)      | Execute events (8 Events)            | ​12060201
1205          | Resolve commercial disputes    | Resolve commercial disputes          | ​12050101

最佳答案

SELECT
  *
 ,AFFECTSID_NEW
  + REPLACE(STR(DENSE_RANK() OVER(PARTITION BY AFFECTSID_NEW ORDER BY activityname​),2),' ','0')
  + REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY AFFECTSID_NEW,activityname ORDER BY actionname​),2),' ','0')
FROM MyTable