下面是一个关于 SELECT
语句的例子,该示例展示了如何使用 CONCAT_WS
和 COLLECT_LIST
函数来处理炸裂之后学生成绩的数据。假设我们有一个名为 test
的表,结构如下:
表结构 test
student_id | student_name | class | score |
---|---|---|---|
1 | Alice | Class1 | 90 |
1 | Alice | Class1 | 85 |
1 | Alice | Class1 | 80 |
2 | Bob | Class2 | 75 |
2 | Bob | Class2 | 80 |
SQL 查询示例
下面的 SQL 查询将使用 CONCAT_WS
和 COLLECT_LIST
函数来合并数据:
SELECT
student_id,
student_name,
CONCAT_WS(',', COLLECT_LIST(score)) AS scores,
CONCAT_WS(' ', class) AS classes
FROM test
GROUP BY student_id, student_name;
查询结果
student_id | student_name | scores | classes |
---|---|---|---|
1 | Alice | 90,85,80 | Class1 |
2 | Bob | 75,80 | Class2 |
说明
-
CONCAT_WS: 在这里我们使用
CONCAT_WS
来将scores
列中的分数合并为一个以逗号分隔的字符串。COLLECT_LIST(score)
将所有分数作为列表收集。 -
GROUP BY: 使用
GROUP BY
子句按照student_id
和student_name
进行分组,这样可以将每个学生的分数合并到一行中。 -
classes: 由于每个学生的班级是相同的,因此可以简单地用
CONCAT_WS
将班级信息合并。
下文将详细介绍 SQL 中的 CONCAT
、CONCAT_WS
、COLLECT_LIST
和 COLLECT_SET
函数,以及它们在实际应用中的区别和用法。
1. 数据合并函数对比
1.1 CONCAT
- 功能: 用于将多个字符串连接成一个字符串。
- 用法: 只支持字符串类型的输入。
示例:
SELECT CONCAT('A', 'B', 'C') AS result;
-- 输出 'ABC'
1.2 CONCAT_WS
- 功能: 用于将多个字符串连接成一个字符串,但可以指定分隔符。
- 用法: 接收一个分隔符作为第一个参数,后续参数为要连接的字符串。
- 注意: 仅适用于字符串类型。
示例:
SELECT CONCAT_WS(',', 'A', 'B', 'C') AS result;
-- 输出 'A,B,C'
1.3 COLLECT_LIST
- 功能: 将多行数据合并为一个数组,保留重复值。
- 用法: 常用于分组查询中。
示例:
SELECT student_id, COLLECT_LIST(score) AS scores
FROM student_scores
GROUP BY student_id;
结果:
student_id | scores |
---|---|
1 | [90, 85, 80] |
2 | [75, 80] |
1.4 COLLECT_SET
- 功能: 将多行数据合并为一个集合,去重。
- 用法: 仅保留唯一值,消除重复元素。
示例:
SELECT student_id, COLLECT_SET(score) AS scores
FROM student_scores
GROUP BY student_id;
结果:
student_id | scores |
---|---|
1 | [90, 85, 80] |
2 | [75, 80] |
2. 主要区别总结
CONCAT vs CONCAT_WS:
- CONCAT: 直接将字符串连接,不支持分隔符。
- CONCAT_WS: 允许指定分隔符,使合并结果更具可读性。
COLLECT_LIST vs COLLECT_SET:
- COLLECT_LIST: 会保留重复值,适用于需要保留所有数据的场景。
- COLLECT_SET: 去重,只保留唯一值,适用于需要消除重复项的场合。
数据类型:
- CONCAT 和 CONCAT_WS: 仅适用于字符串类型。
- COLLECT_LIST 和 COLLECT_SET: 可用于任何数据类型。
3. 适用场景
- 使用 CONCAT: 当你需要简单字符串连接而不需要分隔符时。
- 使用 CONCAT_WS: 当你需要在连接字符串时加入特定分隔符以提高可读性时。
- 使用 COLLECT_LIST: 当你需要合并多行数据并保留所有值(包括重复值)时。
- 使用 COLLECT_SET: 当你需要合并多行数据并确保结果中只有唯一值时。