实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式
wm_concat函数:
函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行
SELECT wm_concat(LAST_NAME) FROM PLCH_EMPLOYEES;
把结果里的逗号替换成"|"
REPLACE(wm_concat(LAST_NAME),',','|') as lastnames
按部门分组合并name
SELECT DEPARTMENT_ID,wm_concat(LAST_NAME) as lastnames
懒人扩展用法:
我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单。查询结果如下:
'CREATE OR REPLACE VIEW AS SELECT ' || WMSYS.WM_CONCAT(COLUMN_NAME) || ' FROM PLCH_EMPLOYEES ;' sqlstr TABLE_NAME = 'PLCH_EMPLOYEES';
行列转换:
pivot
语法:
pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);这个是指定我们的聚合函数
pivot_for_clause: defines the columns to be grouped and pivoted;指定我们需要将行转成列的字段
pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).对pivot_for_clause
指定的列进行过滤,只将指定的行转成列。
数据
查询每个部门有多少名员工赚的钱少于2500,以及有多少人多于或等于2500
(SELECT DEPTNO , CASE when sal > 2500 then 'MORE' else 'LESS' end salary_category FROM EMP) (COUNT(1) FOR salary_category IN ('LESS','MORE'))
查询每个部门每个job总工资
SELECT DEPTNO , SAL,JOB FROM EMP
查询每个部门每个job员工人数以及总工资
SELECT DEPTNO , SAL,JOB FROM EMP count(1) as cnt,SUM(SAL) as sals
查询depno为30的部门里面,job为SALESMAN,MANAGER,CLERK的员工各有多少人以及总工资为多少。
SELECT DEPTNO , SAL,JOB FROM EMP count(1) as cnt,SUM(SAL) as sals (30,'CLERK') AS dep30_CLERK, (30,'MANAGER') AS dep30_MANAGER, (30,'SALESMAN') AS dep30_SALESMAN
unpivot:
语法:
UNPIVOT [INCLUDE|EXCLUDE NULLS]
数据:pivoted_data
deptsal --<-- unpivot_clause FOR saldesc --<-- unpivot_for_clause IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
默认是不包含nulls的,我们通过命令处理nulls的结果
deptsal --<-- unpivot_clause FOR saldesc --<-- unpivot_for_clause IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
使用别名
SELECT job,saldesc,deptsal FOR saldesc IN (d10_sal AS 'SAL TOTAL FOR 10', d20_sal AS 'SAL TOTAL FOR 20', d30_sal AS 'SAL TOTAL FOR 30', d40_sal AS 'SAL TOTAL FOR 40'))
|