admin 管理员组

文章数量: 1087139


2024年3月28日发(作者：sparksql面试题)

多行合并为一行的SQL语句

目录

例一 ........................................................................................................................ 1

例二 ........................................................................................................................ 2

例三 ........................................................................................................................ 3

例四 ........................................................................................................................ 5

例五 ........................................................................................................................ 6

例六 如何将多行数据合并成一行多列 ....................................................................... 7

例七 C# ......................................................................................................................... 12

 例一

表数据：test

no q

------------

1 n1

1 n2

1 n3

1 n4

1 n5

3 t1

3 t2

3 t3

3 t4

3 t5

3 t6

2 m1

语句：

with test as (

SELECT 1 AS No, 'N1' AS q FROM Dual UNION ALL

SELECT 1 AS No, 'N2' AS q FROM Dual UNION ALL

SELECT 1 AS No, 'N3' AS q FROM Dual UNION ALL

SELECT 1 AS No, 'N4' AS q FROM Dual UNION ALL

SELECT 1 AS No, 'N5' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T1' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T2' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T3' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T4' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T5' AS q FROM Dual UNION ALL

SELECT 3 AS No, 'T6' AS q FROM Dual UNION ALL

SELECT 2 AS No, 'M1' AS q FROM Dual

)

SELECT No, Substr(Jg, 2, Length(Jg)) AS Jg

FROM (SELECT No, MAX(Sys_Connect_By_Path(q, ',')) AS Jg

FROM (SELECT No,

q,

Row_Number() Over(PARTITION BY No ORDER BY No, q) Rn

FROM Test)

START WITH Rn = 1

CONNECT BY Rn - 1 = PRIOR Rn

AND No = No

GROUP BY No)

谭工前面那个示例"CONNECT BY Rn - 1 = PRIOR Rn AND No = No"也应改成"CONNECT BY No

||'|'||(Rn - 1) = PRIOR (NO||'|'||Rn)"结果才对。

另外这个SQL只适合在小数据集或索引结果中进行处理，不然要使用全表扫描效率不高。

输出：

1 ,N1,N2,N3,N4,N5

2 ,M1

3 ,T1,T2,T3,T4,T5,T6

 例二

另解：

人员所属部门:第一个部门为缺省部门

SELECT 人员编号,

MIN(姓名) AS 姓名,


本文标签： 部门 数据 使用 结果 处理

版权声明：本文标题：多行合并为一行的SQL语句 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1711591208a601344.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。