根据优先级分配值

Question

我有一列"Designation"，其中有些员工有多个值。所以我只想在其中保留一个值，并根据"指定优先级表"中的优先级分配该值。示例：

Agentdetails:
accountid  designation
1455       owner / Ceo
2035       /Application Consultant/Director
1233       /General / It
1453       /Hrd/It Support/Engineering/It Mgr/ Assistant Marketing Manager 

Designation Priority:
CEO/Application Consultant/IT Mgr/IT    1
Owner/Director                          2
It staff/HRD                            1

所以现在根据优先级，我的代理详细信息表应该是这样的：

accountid  designation
1455       Ceo               ( as ceo is 1 so it should be taken and rest values should be removed)
2035       Application Consultant
1233       It                ( as there is no general and it value is 1)
1453       Hrd               (Here hrd as it's value is 1 and comes first )

让我知道如何在SQL中实现这一点。有什么函数吗？或者我需要创建一个UDF吗？

有什么建议请告诉我。

Answer 1

您必须使用STRING_SPLIT将多值字符串拆分为每个表的单独行，然后将它们连接以获得结果。

尽管以下代码有效，但我建议您对数据模型和数据质量问题进行一些更改：

• 保持列值为原子(第一个Normal Form(
• 以不区分大小写的方式表示值。在这种情况下，指定。如果你有区分大小写的排序规则，这将是一个问题
• 删除指定周围的额外空格，因为这可能导致JOIN失败
• 尽量在指定主表中表示所有指定，因为如果指定表中没有匹配项，可以避免在代理表中出现垃圾指定值

对SQLFiddle 的引用

测试设置

CREATE TABLE AgentDetails
(accountid int, designation VARCHAR(8000))
INSERT INTO AgentDetails
VALUES
('1455','owner / Ceo'),
('2035','/Application Consultant/Director'),
('1233','/General / It'),
('1453','/Hrd/It Support/Engineering/It Mgr/ Assistant Marketing Manager')
;
CREATE TABLE DesignationPriority
(Designation VARCHAR(8000), Priority int)
INSERT INTO DesignationPriority
VALUES
('CEO/Application Consultant/IT Mgr/IT',1),
('Owner/Director', 2),
('It staff/HRD', 1);

查询以执行

;WITH CTE_AgentDetails AS
(
SELECT accountId, TRIM(value) AS Designation
FROM AgentDetails
CROSS APPLY STRING_SPLIT(designation,'/')
WHERE LEN(value) > 0
), CTE_DesignationPriority AS
(
SELECT Priority, TRIM(value) AS Designation
FROM DesignationPriority
CROSS APPLY STRING_SPLIT(Designation,'/')
)
SELECT accountID, Designation
FROM
(
SELECT accountID
,a.Designation
, ROW_NUMBER() OVER(PARTITION BY a.AccountID ORDER BY CASE WHEN p.Priority IS NULL THEN 999 ELSE p.priority END) AS rnk
FROM
CTE_AgentDetails AS a
LEFT OUTER JOIN CTE_DesignationPriority AS p
ON p.Designation = a.Designation
) AS t 
WHERE rnk = 1;

如果您不想要CTE，您可以选择派生表。

SELECT accountID, Designation
FROM
(
SELECT accountID
,a.Designation
, ROW_NUMBER() OVER(PARTITION BY a.AccountID ORDER BY CASE WHEN p.Priority IS NULL THEN 999 ELSE p.priority END) AS rnk
FROM
(SELECT accountId, TRIM(value) AS Designation
FROM AgentDetails
CROSS APPLY STRING_SPLIT(designation,'/')
WHERE LEN(value) > 0)AS a
LEFT OUTER JOIN 
(SELECT Priority, TRIM(value) AS Designation
FROM DesignationPriority
CROSS APPLY STRING_SPLIT(Designation,'/')) AS p
ON p.Designation = a.Designation
) AS t 
WHERE rnk = 1;

结果集

+-----------+------------------------+
| accountID |      Designation       |
+-----------+------------------------+
|      1233 | It                     |
|      1453 | Hrd                    |
|      1455 | Ceo                    |
|      2035 | Application Consultant |
+-----------+------------------------+