【oracle】oracle常用查询操作

1. 行列转换

1.1 行转列

-- 这样可以将行转换为列，sql可以直接进行in查询
SELECT
	REGEXP_SUBSTR( '1,2,3,a,b,c', '[^,]+', 1, LEVEL ) AS item 
FROM
	dual CONNECT BY REGEXP_SUBSTR( '1,2,3,a,b,c', '[^,]+', 1, LEVEL ) IS NOT NULL;

1.2 列转行

-- listagg语法：listagg(expr,delimiter) within group(order by expr)
SELECT
	listagg ( t.item, ',' ) within GROUP ( ORDER BY key_group ) AS items 
FROM
(
SELECT
        REGEXP_SUBSTR( '1,2,3,a,b,c', '[^,]+', 1, LEVEL ) AS item,
        1 AS key_group 
FROM
dual CONNECT BY REGEXP_SUBSTR( '1,2,3,a,b,c', '[^,]+', 1, LEVEL ) IS NOT NULL 
) t

2. 拼接字符串

SELECT  t1.cls_name，LISTAGG(t1.t1.cls_user_name, ',') WITHIN GROUP (ORDER BY t1.cls_user_name) AS "无单引号拼接",
LISTAGG(''''||t1.cls_user_name||'''', ',') WITHIN GROUP (ORDER BY t1.cls_user_name) AS "单引号拼接符拼接的字符串"
FROM cls t1
group by t1.cls_name;

99 其他常用操作

说明	查询SQL
查看当前oracle字符集	select userenv('language') from dual;
查看当前oracle版本	SELECT * FROM v$version;