本文共 2714 字，大约阅读时间需要 9 分钟。

1.替换空值nvl(字段,默认值)

如果字段值非空返回字段值，如果字段为空返回默认值。

select comm,nvl(comm,-1) from emp;+---------+---------+|  comm   |   _c1   |+---------+---------+| NULL    | -1.0    || 300.0   | 300.0   || 500.0   | 500.0   || NULL    | -1.0    || 1400.0  | 1400.0  || NULL    | -1.0    || NULL    | -1.0    || NULL    | -1.0    || NULL    | -1.0    || 0.0     | 0.0     || NULL    | -1.0    || NULL    | -1.0    || NULL    | -1.0    || NULL    | -1.0    |+---------+---------+

2.拼接字符串concat(字段1,‘分隔符’,字段2)

将多个字段按指定分隔符拼接。

+-------------------+----------------------------+-------------------------+| person_info.name  | person_info.constellation  | person_info.blood_type  |+-------------------+----------------------------+-------------------------+| 孙悟空               | 白羊座                        | A                       || 大海                | 射手座                        | A                       || 宋宋                | 白羊座                        | B                       || 猪八戒               | 白羊座                        | A                       || 凤姐                | 射手座                        | A                       |+-------------------+----------------------------+-------------------------+select concat(constellation,',',blood_type) type ,count(*) total from person_info group by constellation,blood_type;+--------+--------+|  type  | total  |+--------+--------+| 射手座,A  | 2      || 白羊座,A  | 2      || 白羊座,B  | 1      |+--------+--------+

3.字段值聚合为数组collection_list(字段)

将多行字段值聚合为一行数组。

select concat(constellation,',',blood_type) type ,collect_list(name) namesfrom person_info group by constellation,blood_type;+--------+----------------+|  type  |     names      |+--------+----------------+| 射手座,A  | ["大海","凤姐"]    || 白羊座,A  | ["孙悟空","猪八戒"]  || 白羊座,B  | ["宋宋"]         |+--------+----------------+

4.用分隔符拼接集合元素concat_ws(‘分隔符’,字段)

select concat(constellation,',',blood_type) type ,concat_ws('|',collect_list(name)) namesfrom person_info group by constellation,blood_type;+--------+----------+|  type  |  names   |+--------+----------+| 射手座,A  | 大海|凤姐    || 白羊座,A  | 孙悟空|猪八戒  || 白羊座,B  | 宋宋       |+--------+----------+

5.切分字段值为数组split(字段,‘分隔符’)

+-------------------+----------------------+| movie_info.movie  | movie_info.category  |+-------------------+----------------------+| 《疑犯追踪》            | 悬疑,动作,科幻,剧情          || 《Lie to me》       | 悬疑,警匪,动作,心理,剧情       || 《战狼2》             | 战争,动作,灾难             |+-------------------+----------------------+select split(category,',') type from movie_info;+-----------------------------+|            type             |+-----------------------------+| ["悬疑","动作","科幻","剧情"]       || ["悬疑","警匪","动作","心理","剧情"]  || ["战争","动作","灾难"]            |+-----------------------------+

转载地址：https://blog.csdn.net/FlatTiger/article/details/113885038 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！