查询语句是:select xwcs
from (select get_user_xwcs(b.id, '2016-11-01', '2016-11-30') as xwcs
from t_user b
where b.area_id = 201
and b.company_id is not null)
这个查询语句是从t_detail_review表中抽取出每个用户从11月1号到11月30号的数据,执行该语句时,当在plsql中展开所有数据时特别慢,影响了效率,求改进方案!
get_user_xwcs函数:
CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=40 then 40 else cs end) into icount from (
select userid,to_char(create_date,'yyyy-mm-dd'),count(distinct dyn_detail_id) cs
from t_detail_review
where userid=user_id
and (startDate is null or create_date>=to_date(startDate,'yyyy-mm-dd'))
and (endDate is null or create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))
group by userid,to_char(create_date,'yyyy-mm-dd'));
RETURN nvl(icount,0);
END;
from (select get_user_xwcs(b.id, '2016-11-01', '2016-11-30') as xwcs
from t_user b
where b.area_id = 201
and b.company_id is not null)
这个查询语句是从t_detail_review表中抽取出每个用户从11月1号到11月30号的数据,执行该语句时,当在plsql中展开所有数据时特别慢,影响了效率,求改进方案!
get_user_xwcs函数:
CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=40 then 40 else cs end) into icount from (
select userid,to_char(create_date,'yyyy-mm-dd'),count(distinct dyn_detail_id) cs
from t_detail_review
where userid=user_id
and (startDate is null or create_date>=to_date(startDate,'yyyy-mm-dd'))
and (endDate is null or create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))
group by userid,to_char(create_date,'yyyy-mm-dd'));
RETURN nvl(icount,0);
END;
解决方案 »
- oracle新手求教,连接oracle后,Backspace和Delete键这些无法使用
- 这样写怎么不行
- 展开表应该如何写?
- 请教一个比较复杂汇总SQL的写法?
- 求一条sql语句,在线等.急......
- 请教在数据库中写入标点符号的问题,急!!!
- 关于oracle的连接池,方便的来说两句?(100分相送)
- 在触发器中如何判断某一次update只改变了某一个字段
- oracle的增量备份如何实现?
- oracle 跨平台迁移 linux 到windows求助请教
- oracle 存储过程 Error: PLS-00103: 出现符号 "GETALLPAYMENTAPPLY"在需要下列之一时: if
- weblogic控制台无法通过ip地址访问
t_user:t_detail_review:
你目前的语句会导致全表扫描另外,你的函数好像有问题存在返回多条记录的的情况
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=40 then 40 else cs end) into icount from (
select userid,count(distinct dyn_detail_id) cs
from t_detail_review
where userid=user_id
and create_date>=to_date(startDate,'yyyy-mm-dd')
and create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by userid);
RETURN nvl(icount,0);
END;
with t as
(select userid,
sum(case
when cs >= 40 then
40
else
cs
end) xwcs
from (select userid, trunc(create_date), count(distinct dyn_detail_id) cs
from t_detail_review
where create_date between to_date(startdate, 'yyyy-mm-dd') and
to_date(enddate || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by userid, trunc(create_date))
group by userid)
select b.id, c.xwcs
from t_user b
left join t c
on b.id = c.userid
where b.area_id = 201
and b.company_id is not null
2、我看这个语句,好像真不必用get_user_xwcs这个函数,其实就是两个表t_user和t_detail_review做下join的事情,加个函数中转下可能浪费了效率,把问题搞复杂了