由一个SQL问题引发的：WM_CONCAT、LISTAGG的用法

Author Avatar
ebichu 7月 23, 2019
  • 在其它设备中阅读本文章

详细数据如图一，含义为名字为甲乙丙的三人不限次数任意参加python、c++、java考试，得到的分数等级在第三列。

现需要筛选出只参过一种考试的人名和科目，结果应如图二。在oracle下SQL该如何写？

图一

图二

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE temp_table (name varchar(255), course varchar(255), score varchar(255));
INSERT INTO temp_table VALUES ('甲', 'python', 'c');
INSERT INTO temp_table VALUES ('甲', 'python', 'b');
INSERT INTO temp_table VALUES ('甲', 'python', 'a');
INSERT INTO temp_table VALUES ('乙', 'python', 'a');
INSERT INTO temp_table VALUES ('乙', 'c++', 'a');
INSERT INTO temp_table VALUES ('丙', 'python', 'c');
INSERT INTO temp_table VALUES ('丙', 'java', 'c');
INSERT INTO temp_table VALUES ('丙', 'java', 'b');
INSERT INTO temp_table VALUES ('丙', 'java', 'a');
SELECT * FROM temp_table;

思路一：首先在name、course两列做去重处理。然后以name做分组，过滤出统计数量为一的记录。最后在进行关联得到name、course两列。

A1:最后关联的步骤使用join连接的方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
	DISTINCT t.name,
	tt.course
FROM 
	(SELECT 
		name
	FROM (
		SELECT DISTINCT name, course 
		FROM TEMP_TABLE
		) 
	GROUP BY name
	HAVING count(1) = 1) t 
INNER JOIN TEMP_TABLE tt ON t.name = tt.name

A2:感觉A1的方式有点啰嗦，所以最后的步骤使用的嵌套子查询。

1
2
3
4
5
6
7
8
9
10
SELECT
	t.name,
	(SELECT DISTINCT course FROM TEMP_TABLE WHERE name = t.name) course
FROM (
	SELECT 
		DISTINCT name, 
		course
	FROM TEMP_TABLE) t
GROUP BY t.name
HAVING count(1) = 1

觉得思路一总的来看还是有点啰嗦，故发给小伙伴们讨论更优解。思路二就是来自小伙伴的想法。

思路二：首先使用函数把name相同的course列以特殊字符拼接在一行中，然后在此基础上过滤出没有特殊字符的记录。

A3:使用WM_CONCAT函数，可以在函数中直接做去重处理。

1
2
3
4
5
6
7
8
9
SELECT 
	name,
	course
FROM (
	SELECT name, WM_CONCAT(DISTINCT course) course 
	FROM TEMP_TABLE t 
	GROUP BY t.name
) 
WHERE course NOT LIKE  '%,%'

A4:使用LISTAGG函数，要提前做去重处理再使用函数。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	name,
	course
FROM (
	SELECT t.name, LISTAGG(t.course, ';') WITHIN GROUP (ORDER BY t.name, t.course) course
	FROM (
	SELECT DISTINCT name, course 
	FROM TEMP_TABLE
	) t 
	GROUP BY t.name
) 
WHERE course NOT LIKE  '%;%'

总结：WM_CONCAT、LISTAGG的用法
  • WM_CONCAT不出现在12c中，所以不建议使用这个函数。但是还是列出了它的不同用法。
1
2
3
4
5
6
SELECT 
	name, 
	WM_CONCAT(course) course 
FROM 
	TEMP_TABLE t 
GROUP BY t.name

1
2
3
4
5
6
SELECT 
	name, 
	WM_CONCAT(DISTINCT course) course 
FROM 
	TEMP_TABLE t 
GROUP BY t.name

  • LISTAGG在11.2版本之后可以使用。

对于指定的列，LISTAGG对子ORDER BY句中指定的每个组内的数据进行排序，然后连接列的值。

  • 作为单集聚合函数，LISTAGG对所有行进行操作并返回单个输出行。
  • 作为一个组集聚合，该函数操作并返回该GROUP BY子句定义的每个组的输出行。
  • 作为分析函数，LISTAGG根据中的一个或多个表达式将查询结果集划分为组query_partition_clause。

函数的参数遵循以下规则：

  • measure_expr可以是任何列。度量列中的空值将被忽略。
  • delimiter_expr指定是分开测量的值的字符串。该子句是可选的，默认为NULL。
  • 在order_by_clause决定了被返回连接值的顺序。仅当ORDER BY列列表实现唯一排序时，该功能才是确定性的。

如果列是RAW类型，返回数据类型是RAW，否则返回值是VARCHAR2类型。