需求描述:有一个班级表,有各个班级的信息,需要取到每个班级对应的学生的信息,每个班级信息作为一条记录输出。
Oracle 为我们提供了一个函数实现这种需求 LISTAGG
使用详解:
1、创建一个班级表classDate,表内有如下两个班级的信息: 1班 和 2班
2、在sql中实现需求
-- 用【临时表空间】代替表classDate
WITH classDate AS (
SELECT 1 classId, '张一' AS name, 15 age FROM dual
UNION ALL
SELECT 1 classId, '李一' AS name, 13 age FROM dual
UNION ALL
SELECT 1 classId, '王一' AS name, 12 age FROM dual
UNION ALL
SELECT 2 classId, '陈二' AS name, 12 age FROM dual
UNION ALL
SELECT 1 classId, '高一' AS name, 13 age FROM dual
UNION ALL
SELECT 2 classId, '佟二' AS name, 14 age FROM dual
)
SELECT
classId
-- “||” :Oracle中拼接字符串
, LISTAGG(name || '(' || age || '),') AS nameAndAge -- 将name和age拼接起来
FROM
classDate
GROUP BY
classId
ORDER BY
classId
3、上面sql的输出结果