文章目录
基础方法介绍
拼接的字符串没有超过4000字符
SELECT department_id AS "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;
SELECT department_id AS "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date)
OVER (PARTITION BY department_id) AS "Employees"
FROM employees
ORDER BY department_id;
拼接的字符串超过4000字符,转为CLOB 字段处理
SELECT department_id AS "Dept.",
xmlagg(xmlparse(content last_name||’,’ wellformed ) ORDER BY hire_date).getclobval()
AS "Employees"
FROM employees
GROUP BY department_id;
ORA-01489: result of string concatenation is too long 解决思路
Oracle 12.2 以及以上
select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;
Oracle 11.2至12.1
分析函数可以生成字符串聚合的运行总长度。然后,内联视图可以删除长度大于4000的所有值。
在实际查询中,可能需要在分析函数添加partition by 以仅对每个组进行计数。
select
--Save a little space for a ' ...' to imply that there is more data not shown.
case when max(total_length) > 3996 then
listagg(product_name, ', ') within group (order by product_name)||
' ...'
else
listagg(product_name, ', ') within group (order by product_name)
end product_names
from
(
--Get names and count lengths.
select
product_name,
--Add 2 for delimiters.
sum(length(product_name) + 2) over (order by product_name) running_length,
sum(length(product_name) + 2) over () total_length
from products
order by product_name
)
where running_length <= 3996
OVER(PARTITION BY)函数相关
测试数据如下:
create table T2_TEMP(
NAME varchar2(10) primary key,
CLASS varchar2(10),
SROCE NUMBER
)
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('cfe', '2', 74);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('dss', '1', 95);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ffd', '1', 95);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('fda', '1', 80);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gds', '2', 92);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gf', '3', 99);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ddd', '3', 99);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('adf', '3', 45);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('asdf', '3', 55);
insert into T2_TEMP (NAME, CLASS, SROCE)
values ('3dd', '3', 78);
(1)、查询每个班的第一名的成绩:如下
注意:在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。
1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEM
(2)、rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。dense_rank()是连续排序,有两个第二名时仍然跟着第三名
select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
(3)
select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
(4)
select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
count() over(partition by … order by …):求分组后的总数。
max() over(partition by … order by …):求分组后的最大值。
min() over(partition by … order by …):求分组后的最小值。
avg() over(partition by … order by …):求分组后的平均值。
lag() over(partition by … order by …):取出前n行数据。
lead() over(partition by … order by …):取出后n行数据。
ratio_to_report() over(partition by … order by …):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。
percent_rank() over(partition by … order by …):