介绍基于三列的4位数字

Question

我有一个有趣的问题，我想为一个表生成一个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位数字

1. 同一"Affectsid_new“和唯一"activityname”的​​应为01，02，03.
2. 对于非唯一的"activityname“和"activityname"="actionname”，如果"Affectsid_new“也是非唯一的，则应该是从上面第1点开始的连续数字，或者如果"Affectsid_new”是唯一的，则应该是"01“。

​一旦生成第5位和第6位，以下是生成第7位和第8位的条件

1. 同一"Affectsid_new“和唯一"activityname”的​​应为"01“
2. 对于非唯一的"activityname“和"activityname"="actionname”，应该是"01“。
3. 对于非唯一的"activityname“和"activityname"≠"actionname”(不相等)，它应该是上述第2点(02,03,04..)的连续数字。如果"Affectsid_new“也是非唯一的，或者如果"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

Answer 1

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

Answer 2

请尝试以下未注释的查询。我试着实现你所需要的，但结果与你在这里给出的例子不完全一样。结果取决于您选择排序的列：

SELECT *,
(AFFECTSID_NEW +
 RIGHT('00' + CONVERT(NVARCHAR, RANK() OVER (PARTITION BY AFFECTSID_NEW ORDER BY activityname)), 2) +
 RIGHT('00' + CONVERT(NVARCHAR, RANK() OVER (PARTITION BY activityname ORDER BY actionname)), 2)) AS Expected
FROM #TempTable

下面是表创建脚本(以防万一)。

--CREATE TABLE #TempTable (AFFECTSID_NEW NVARCHAR(8), activityname NVARCHAR(256), actionname NVARCHAR(256))

--INSERT INTO #TempTable
--VALUES
--('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')

希望这能帮上忙。