如何在SQL中将两次查询的多字段多行结果拼接至同一行

实现逻辑 #

你需要先将第一次查询作为主表，提取lien字段中的查询参数值作为第二次查询的过滤条件，再用字符串聚合函数将第二次返回的多行结果按要求格式拼接，最后和第一次查询的字段合并即可。

不同数据库实现示例 #

SQL Server 版本 #

WITH first_query AS (
    -- 第一次查询结果，提取lien中的id参数
    SELECT 
        lien, 
        id AS outil_id,
        CASE WHEN inactif = 0 THEN 'Oui' ELSE 'Non' END AS Actif,
        SUBSTRING(lien, CHARINDEX('?id=', lien) + 4, LEN(lien)) AS link_param_id
    FROM redirection.elementsOutils 
    WHERE lien LIKE '%saaprod/soutien_tache/redirection.asp%'
    ORDER BY inactif, dateMAJ
),
second_query AS (
    -- 关联第一次查询的参数过滤第二次查询结果
    SELECT 
        epa.id,
        epa.clics AS total_clics,
        epa.clicsAnneeActuelle,
        epa.nbEmployesAnneActuelle,
        epa.dateMAJ,
        CASE WHEN epa.inactif = 0 THEN 'Oui' ELSE 'Non' END AS Actif,
        fq.lien,
        fq.outil_id,
        fq.Actif AS outil_actif
    FROM redirection.elementsParAnnee('2021-01-01 00:00:00.0') epa
    CROSS JOIN first_query fq
    WHERE epa.id > 0 
        AND (epa.nom LIKE '%' + fq.link_param_id + '%'
             OR epa.id LIKE '%' + fq.link_param_id + '%' 
             OR epa.lien LIKE '%' + fq.link_param_id + '%')
)
-- 聚合拼接第二次查询结果
SELECT 
    lien,
    outil_id AS id,
    outil_actif AS Actif,
    STRING_AGG(
        '{id:' + CAST(id AS VARCHAR(10)) + 
        ', clics:' + CAST(total_clics AS VARCHAR(10)) +
        ', clicsAnneeActuelle:' + CAST(clicsAnneeActuelle AS VARCHAR(10)) +
        ', nbEmployesAnneeActuelle:' + CAST(nbEmployesAnneActuelle AS VARCHAR(10)) +
        ', dateMAJ:' + ISNULL(CAST(dateMAJ AS VARCHAR(20)), 'NULL') +
        ', Actif:' + Actif + '}',
        '; '
    ) AS Infos
FROM second_query
GROUP BY lien, outil_id, outil_actif

MySQL 版本 #

WITH first_query AS (
    SELECT 
        lien, 
        id AS outil_id,
        CASE WHEN inactif = 0 THEN 'Oui' ELSE 'Non' END AS Actif,
        SUBSTRING_INDEX(lien, '?id=', -1) AS link_param_id
    FROM redirection.elementsOutils 
    WHERE lien LIKE '%saaprod/soutien_tache/redirection.asp%'
    ORDER BY inactif, dateMAJ
),
second_query AS (
    SELECT 
        epa.id,
        epa.clics AS total_clics,
        epa.clicsAnneeActuelle,
        epa.nbEmployesAnneActuelle,
        epa.dateMAJ,
        CASE WHEN epa.inactif = 0 THEN 'Oui' ELSE 'Non' END AS Actif,
        fq.lien,
        fq.outil_id,
        fq.Actif AS outil_actif
    FROM redirection.elementsParAnnee('2021-01-01 00:00:00.0') epa
    JOIN first_query fq
    WHERE epa.id > 0 
        AND (epa.nom LIKE CONCAT('%', fq.link_param_id, '%')
             OR epa.id LIKE CONCAT('%', fq.link_param_id, '%') 
             OR epa.lien LIKE CONCAT('%', fq.link_param_id, '%'))
)
SELECT 
    lien,
    outil_id AS id,
    outil_actif AS Actif,
    GROUP_CONCAT(
        CONCAT(
            '{id:', id,
            ', clics:', total_clics,
            ', clicsAnneeActuelle:', clicsAnneeActuelle,
            ', nbEmployesAnneeActuelle:', nbEmployesAnneActuelle,
            ', dateMAJ:', IFNULL(dateMAJ, 'NULL'),
            ', Actif:', Actif, '}'
        ) SEPARATOR '; '
    ) AS Infos
FROM second_query
GROUP BY lien, outil_id, outil_actif

注意事项 #

• 你预期结果中写的clcis是拼写错误，代码中已修正为正确的clics
• 若数据库不支持CTE语法，可将子查询直接嵌套到FROM子句中使用
• 字段类型转换逻辑可根据实际表的字段类型调整，避免类型转换报错
• 若第一次查询返回多行数据，该代码也会自动匹配每一行的参数查询对应第二次的结果

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