【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

99 其他常用操作

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