Oracle常用操作

1、将一行单元格中以'、'分隔的字符串, 拆分为多行

SELECT DISTINCT
	t.xh,LEVEL,REGEXP_SUBSTR(t.xh,'[^、]', 1, level) as xhmc
FROM
	v t
CONNECT BY
	LEVEL <= LENGTH(t.xh) - LENGTH(REPLACE(t.xh, '、', '')) +1

2、生成树形结构数据

SELECT
	t.xh, PRIOR t.xh,LEVEL, CONNECT_BY_ROOT(), SYS_CONNECT_BY_PATH(t.xh, '/'), CONNECT_BY_ISLEAF()
FROM
	v t
START WITH
	LENGTH(t.xh) = 2
CONNECT BY NOCYCLE
	PRIOR t.xh = SUBSTR(t.xh, 0, LENGTH(t.xh)-2)

3、相同key合并value, key可以是多字段

SELECT
	t.column1, t.column2, LISTAGG(t.column3, ',') WITHIN GROUP (ORDER BY t.column1) AS AGG_VALUE
FROM
	v t
GROUP BY
	t.column1, t.column2

4、利用row_number()去重

SELECT
	*
FROM   
	(SELECT
		row_number() OVER(PARTITION BY 身份证号码 ORDER BY 最后更新时间 DESC) RK, t.*  
	FROM emp) T
WHERE
	T.RK > 1