如何基于Directive列拆分分组Norms列并构建数据库视图？

我从数据库中提取了Directive和Norms两列，希望基于Directive列拆分后的值对Norms列进行分组。由于Norms与指令的关联关系仅存在于Excel文件中，我需要构建一个数据库视图来实现该关联逻辑。

原始数据 #

Directive           |      Norms       
 CE, 650, No Flicker | CEI EN 60598-2-22  
 CE, 650, No Flicker | CEI EN 60598-2-2  
 CE, 650, No Flicker | CEI EN 60598-1   
 CE, 650, No Flicker | EN 62471         
 CE, 650, No Flicker | 2009/125/CE      
 CE, 650, No Flicker | 874/2012/CE       
 CE, 650, No Flicker | 2014/30/EU       
 CE, 650, No Flicker | 2014/35/EU       
 CE, 650, No Flicker | ISO 15714        
 CE, 650, No Flicker | EN 60335-2-65  

期望结果 #

Directive       |              Norms                     
 CE              | CEI EN 60598-2-22, CEI EN 60598-2-2, CEI EN60598-1  
 650             | 2009/125/CE, 874/2012/CE, 2014/30/EU,2014/35/EU   
 No Flicker      | EN 62471, ISO 15714, EN 60335-2-65  

实现方案 #

1. MySQL（8.0+） #

通过JSON_TABLE拆分Directive列，再用GROUP_CONCAT聚合Norms：

CREATE VIEW directive_norms AS
SELECT 
    TRIM(d.directive_item) AS Directive,
    GROUP_CONCAT(DISTINCT n.Norms ORDER BY n.Norms SEPARATOR ', ') AS Norms
FROM 
    your_table_name n,
    JSON_TABLE(
        CONCAT('["', REPLACE(n.Directive, ', ', '","'), '"]'),
        '$[*]' COLUMNS (directive_item VARCHAR(255) PATH '$')
    ) d
WHERE 
    -- 根据Excel中的关联关系调整匹配规则
    (d.directive_item = 'CE' AND n.Norms LIKE 'CEI EN%')
    OR (d.directive_item = '650' AND (n.Norms LIKE '%/%CE' OR n.Norms LIKE '%/%EU'))
    OR (d.directive_item = 'No Flicker' AND n.Norms IN ('EN 62471', 'ISO 15714', 'EN 60335-2-65'))
GROUP BY 
    TRIM(d.directive_item);

2. PostgreSQL #

用STRING_TO_ARRAY拆分字符串，STRING_AGG聚合结果：

CREATE VIEW directive_norms AS
SELECT 
    TRIM(d.directive_item) AS Directive,
    STRING_AGG(DISTINCT n.Norms, ', ' ORDER BY n.Norms) AS Norms
FROM 
    your_table_name n,
    UNNEST(STRING_TO_ARRAY(n.Directive, ', ')) AS d(directive_item)
WHERE 
    -- 匹配规则需与Excel中的关联逻辑一致
    (d.directive_item = 'CE' AND n.Norms LIKE 'CEI EN%')
    OR (d.directive_item = '650' AND (n.Norms LIKE '%/%CE' OR n.Norms LIKE '%/%EU'))
    OR (d.directive_item = 'No Flicker' AND n.Norms IN ('EN 62471', 'ISO 15714', 'EN 60335-2-65'))
GROUP BY 
    TRIM(d.directive_item);

3. SQL Server #

借助STRING_SPLIT拆分列，STRING_AGG聚合Norms：

CREATE VIEW directive_norms AS
SELECT 
    TRIM(d.value) AS Directive,
    STRING_AGG(DISTINCT n.Norms, ', ') WITHIN GROUP (ORDER BY n.Norms) AS Norms
FROM 
    your_table_name n
CROSS APPLY 
    STRING_SPLIT(n.Directive, ', ') d
WHERE 
    -- 按Excel中的关联关系设置匹配条件
    (d.value = 'CE' AND n.Norms LIKE 'CEI EN%')
    OR (d.value = '650' AND (n.Norms LIKE '%/%CE' OR n.Norms LIKE '%/%EU'))
    OR (d.value = 'No Flicker' AND n.Norms IN ('EN 62471', 'ISO 15714', 'EN 60335-2-65'))
GROUP BY 
    TRIM(d.value);

注意：将your_table_name替换为实际表名，并根据Excel中的具体关联逻辑调整WHERE子句的匹配规则。

内容的提问来源于stack exchange，提问作者Aamir