保存在这里,方便自己以后查看 (⊙_⊙) ╮(╯▽╰)╭
我们可以先这样创建一张需要的临时表(Role 表):
select 'role_01' as role_id, 'admin' as role_category from dual
union all
select 'role_02' as role_id, 'admin' as role_category from dual
union all
select 'role_03' as role_id, 'normal' as role_category from dual
union all
select 'role_04' as role_id, 'normal' as role_category from dual
union all
select 'role_05' as role_id, 'normal' as role_category from dual
union all
select 'role_06' as role_id, 'normal' as role_category from dual
union all
select 'role_07' as role_id, 'normal' as role_category from dual
union all
select 'role_08' as role_id, 'normal' as role_category from dual
表结构:
现在我们要根据 role_category 分组,把相同 role_category 下的 role_id 连接起来,就是列 role_id 转成行,大家可能很容易想到使用 wm_concat() 方法来进行:
select role_category,
wm_concat(role_id) as role_id_list
from (
select 'role_01' as role_id, 'admin' as role_category from dual
union all
select 'role_02' as role_id, 'admin' as role_category from dual
union all
select 'role_03' as role_id, 'normal' as role_category from dual
union all
select 'role_04' as role_id, 'normal' as role_category from dual
union all
select 'role_05' as role_id, 'normal' as role_category from dual
union all
select 'role_06' as role_id, 'normal' as role_category from dual
union all
select 'role_07' as role_id, 'normal' as role_category from dual
union all
select 'role_08' as role_id, 'normal' as role_category from dual
)
group by role_category
好了,我们得到了查询结果:
但是这个结果是否有点问题呢?
我们发现第2行第2列的数据其 role_id 并不是按照从小到大排序的,似乎并不那么完美,得修改一下sql代码:
-- partition by role_category order by role_id 表示按照 role_category 分组,按照 role_id 排序
select role_category,
wm_concat(role_id) over(partition by role_category order by role_id)
as role_id_list
from (
select 'role_01' as role_id, 'admin' as role_category from dual
union all
select 'role_02' as role_id, 'admin' as role_category from dual
union all
select 'role_03' as role_id, 'normal' as role_category from dual
union all
select 'role_04' as role_id, 'normal' as role_category from dual
union all
select 'role_05' as role_id, 'normal' as role_category from dual
union all
select 'role_06' as role_id, 'normal' as role_category from dual
union all
select 'role_07' as role_id, 'normal' as role_category from dual
union all
select 'role_08' as role_id, 'normal' as role_category from dual
)
现在的查询结果:
这次的 role_id 都是排好序的,但是查询结果太多了,我们需要的只是最长的那一行数据,在 sql 中加一个 max() 方法就可以了:
select role_category,
max(role_id_list)
from (
select role_category,
wm_concat(role_id) over(partition by role_category order by role_id)
as role_id_list
from (
select 'role_01' as role_id, 'admin' as role_category from dual
union all
select 'role_02' as role_id, 'admin' as role_category from dual
union all
select 'role_03' as role_id, 'normal' as role_category from dual
union all
select 'role_04' as role_id, 'normal' as role_category from dual
union all
select 'role_05' as role_id, 'normal' as role_category from dual
union all
select 'role_06' as role_id, 'normal' as role_category from dual
union all
select 'role_07' as role_id, 'normal' as role_category from dual
union all
select 'role_08' as role_id, 'normal' as role_category from dual
)
)
group by role_category
这次我们得到了想要的结果:
但是为了得到这两条数据我们查出的数据多了点,而且又用了一次 max() 方法,这在效率上肯定是很低的
wm_concat() 是系统函数,也许有的用户登录相关 Oracle 数据库得不到该函数的使用权限(比如我就是)
下面再来看一种能够实现同样功能,但是不使用 wm_concat() 函数的方法。
同样是使用最开始定义的那张临时表,我们先作一下查询,增加一列 row_number 用来记录 role_category 分组下的 role_id 序号,这里用到了系统函数 row_number():
select role_category,
role_id,
(row_number() over(partition by role_category order by role_id)) as row_number
from (
select 'role_01' as role_id, 'admin' as role_category from dual
union all
select 'role_02' as role_id, 'admin' as role_category from dual
union all
select 'role_03' as role_id, 'normal' as role_category from dual
union all
select 'role_04' as role_id, 'normal' as role_category from dual
union all
select 'role_05' as role_id, 'normal' as role_category from dual
union all
select 'role_06' as role_id, 'normal' as role_category from dual
union all
select 'role_07' as role_id, 'normal' as role_category from dual
union all
select 'role_08' as role_id, 'normal' as role_category from dual
)
查询结果:
现在我们根据 row_number 把相同 role_category 的 role_id 用系统函数 sys_connect_by_path() 连接起来:
select role_category,
sys_connect_by_path(role_id, ',') as role_id_list
from (
select role_category,
role_id,
(row_number() over(partition by role_category order by role_id))
as row_number
from (
select 'role_01' as role_id ,'admin' as role_category from dual
union all
select 'role_02' as role_id ,'admin' as role_category from dual
union all
select 'role_03' as role_id ,'normal' as role_category from dual
union all
select 'role_04' as role_id ,'normal' as role_category from dual
union all
select 'role_05' as role_id ,'normal' as role_category from dual
union all
select 'role_06' as role_id ,'normal' as role_category from dual
union all
select 'role_07' as role_id ,'normal' as role_category from dual
union all
select 'role_08' as role_id ,'normal' as role_category from dual
)
)
where connect_by_isleaf = 1
start with row_number = 1
connect by row_number - 1 = prior row_number
and role_category = prior role_category
查询结果:
开头多余了一个逗号,最后再用系统函数 ltrim() 来去掉这个逗号就OK了:
select role_category,
ltrim(sys_connect_by_path(role_id, ','), ',') as role_id_list
from (
select role_category,
role_id,
(row_number() over(partition by role_category order by role_id))
as row_number
from (
select 'role_01' as role_id ,'admin' as role_category from dual
union all
select 'role_02' as role_id ,'admin' as role_category from dual
union all
select 'role_03' as role_id ,'normal' as role_category from dual
union all
select 'role_04' as role_id ,'normal' as role_category from dual
union all
select 'role_05' as role_id ,'normal' as role_category from dual
union all
select 'role_06' as role_id ,'normal' as role_category from dual
union all
select 'role_07' as role_id ,'normal' as role_category from dual
union all
select 'role_08' as role_id ,'normal' as role_category from dual
)
)
where connect_by_isleaf = 1
start with row_number = 1
connect by row_number - 1 = prior row_number
and role_category = prior role_category
分享到:
相关推荐
oracle实现行转列功能,并使用逗号进行隔开拼接,成为一条数据.pdf
oracle查询表的所有列并用逗号隔开
Oracle多行记录合并/连接/聚合字符串的几种方法
oracle存储过程中入参是逗号分隔,并且参数要使用在in过滤语句中查询数据。处理的方法与实现
SQL语句用with将列分割成多列存为临时表,再将多行某个字段拼接合并为一行
主流数据(ORACLE,DB2)行转列实例,包括2种实现方式,通过case..when和left join..on;大牛补充其他的实现方式。
适用于数据库使用
2.7拼接运算符 || 6 2.8文字字符串 6 2.9消除重复行 6 2.10其他注意事项 6 三、 SQL语句的处理过程 7 3.1 SQL语句处理过程 7 3.2处理一条select语句 7 四、 where子句 8 4.1 where子句后面可以跟什么 8 4.2语法和...
中以下列要求显示数据列,该如何实现,写出相应格式化命令。 a)EMPLOYEE_ID列相对于列宽右对齐显示列名“员工编号”,并 在显示编号时以4位数 字显示,当不足4位数字时,前端以添加0 补齐。 b)SALARY列要求...
4.3.3 修改数据表中的列 4.3.4 删除数据表中的列 4.4 数据表的删除 4.4.1 删除数据表的语句结构 4.4.2 使用SQL语句删除数据表 4.5 数据表的重命名 4.5.1 重命名数据表的语句 4.5.2 使用SQL语句重命名数据表 4.6 小结...
本书涉及不同平台上数据的排序、过滤和分组,以及表、视图、联结、子查询、游标、存储过程和触发器等内容,通过本书读者可以系统地学习到sql 的知识和方法。 本书注重实用性,操作性很强,适合于sql 的初学者学习...
4.6.4 字符串的拼接 4.6.5 计算字段的其他用途 4.7 不从实体表中取的数据 4.8 联合结果集 4.8.1 简单的结果集联合 4.8.2 联合结果集的原则 4.8.3 UNION ALL 4.8.4 联合结果集应用举例 第5章 函数 ...
4.6.4 字符串的拼接 4.6.5 计算字段的其他用途 4.7 不从实体表中取的数据 4.8 联合结果集 4.8.1 简单的结果集联合 4.8.2 联合结果集的原则 4.8.3 UNION ALL 4.8.4 联合结果集应用举例 第5章 函数 ...
4.6.4 字符串的拼接 4.6.5 计算字段的其他用途 4.7 不从实体表中取的数据 4.8 联合结果集 4.8.1 简单的结果集联合 4.8.2 联合结果集的原则 4.8.3 UNION ALL 4.8.4 联合结果集应用举例 第5章 函数 ...
4.6.4 字符串的拼接 4.6.5 计算字段的其他用途 4.7 不从实体表中取的数据 4.8 联合结果集 4.8.1 简单的结果集联合 4.8.2 联合结果集的原则 4.8.3 UNION ALL 4.8.4 联合结果集应用举例 第5章 函数 ...
9.3.4 CowNewSQL的使用方法 第10章 高级话题 10.1 SQL注入漏洞攻防 10.1.1 SQL注入漏洞原理 10.1.2 过滤敏感字符 10.1.3 使用参数化SQL 10.2 SQL调优 10.2.1 SQL调优的基本原则 10.2.2 索引 ...
查询Oracle数据库数据</summary> <param name="sql">SQL语句</param> 厂区</param> <returns>DataTable数据结果集</returns> <remarks /> - <member name="M:Pic_Chart_Load.SQL_LINK.Oracle_Exe_Sql...
3、对列起别名:有直接起别名,加AS起别名,用双引号起别名等三种方法 (单引号,引起字符串;双引号,引起别名。起别名有符号,或者区分大小写时,必须用双引号) 多表查询时,可给表起别名。(给列起别名,列<空格...
属性不依赖于其它非主属性,确保数据表中的每一列数据都和主键直接相关,而不能间接相关,即要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 1.5.3.2 举例 党员表 党员Id 党员姓名 组织Code 符合3NF ...
1.1.3 列和数据类型..... 3 1.1.4 行..... 4 1.1.5 主键..... 4 1.2 什么是SQL... 5 1.3 动手实践..... 6 1.4 小结..... 7 第2章 检索数据...... 8 2.1 SELECT语句..... 8 2.2 检索单个列..... 9 2.3 检索多个列...