SQL 根据姓名查询工号行集合展示并保持原有顺序

xk1378522

已于 2024-04-02 15:49:49 修改

阅读量163

点赞数 3

分类专栏： sql 文章标签： sql 数据库 excel

于 2024-03-21 18:34:54 首次发布

本文链接：https://blog.csdn.net/xk1378522/article/details/136916574

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文描述了如何通过在Excel中使用SQL查询，对包含人员姓名和工号的历史数据进行清洗和转换，确保导入系统时按照原始顺序匹配工号。重点在于行转列操作、listagg函数的使用以及LEFTJOIN来解决无数据时的空行问题。

摘要由CSDN通过智能技术生成

背景：期初数据导入系统，需清洗历史数据，历史数据中包含人员姓名，导入系统时需使用员工工号进行导入（姓名有可能会重复），历史数据中一个单元格保存多个人员姓名，需匹配出相应的工号并保持原有顺序：

内部人员	期望处理后结果
张三;李四;王五	张三/111111;李四/33333;王五/4444444
二黑;狗蛋
张二;李三;王四	张二/222222;张二/900002;李三/43333;王四/5444444
张三;李四;王五	张三/111111;李四/33333;王五/4444444
张三;李四;王五	张三/111111;李四/33333;王五/4444444

首先通过Excel的公式自动生成SQL（详见尾部扩展点），单个SQL解析如下：

废话不多说，直接上SQL：

SELECT *
FROM 
    (SELECT listagg(zz,';') within group (order by a) AS name --行拼接
    FROM 
        (SELECT zz,
         rownum AS a --解决查询后行拼接乱序问题
        FROM 
           (SELECT nick_name||'/'||user_code AS zz --拼接姓名与工号
            FROM sys_user
                WHERE nick_name IN ('张三','李四','王五')
                   ORDER BY  instr('张三,李四,王五',nick_name) ) --用于按查询内容排序
        )
    ) 
    LEFT JOIN dual ON 1=1 --解决未查到数据时没有空行，增加无数据时空行占位，否则数据行数与原数据不一致

关键点：

行转列，拼接为一个字段；listagg(字段,';') within group (order by 字段)
保持（in）原查询顺序；
行专列拼接按顺序展示（可以指定order by 字段)
查询无数据时，无空行导致行错位与原数据行不一致；（LEFT JOIN dual ON 1=1）

扩展点：

利用Excel生成动态SQL批量查询

内部人员（查询in用的格式）	排序用的格式	处理后的结果	SQL公式
张三','李四','王五	张三,李四,王五	张三/111111;李四/33333;王五/4444444	=concatenate（好长呀，懒得敲了，大概就是这个意思 union all
wei','yue	wei,yue		=concatenate（好长呀，懒得敲了，大概就是这个意思 union all