select get_user_spcs(b.id, '2016-11-01', '2016-11-30') spcs from
t_user b
where b.area_id = 201
and company_id is not null这个函数查询效率很不好
get_user_spcs函数内容为:CREATE OR REPLACE FUNCTION get_user_spcs(userId number,startDate varchar,endDate varchar)
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=20 then 20 else cs end) into icount from (
select user_id,to_char(create_date,'yyyy-mm-dd'),count(distinct video_id) cs
from t_video_review
where user_id=userId
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 user_id,to_char(create_date,'yyyy-mm-dd'));
RETURN nvl(icount,0);
END;
函数大意是统计从11月1号到31号的每个用户对视频的评论次数,
如果要改用left outer join 的方式,该怎么改?
像这个语句就用了这个方式select * from t_user a left ourer join (select count(1) as dz1,userid
from t_praise
where createtime >=to_date('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and createtime <=to_date('2016-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by userid) b on a.id=b.userid
where b.area_id = 201 and nvl(company_id,' ')<>' '
t_user b
where b.area_id = 201
and company_id is not null这个函数查询效率很不好
get_user_spcs函数内容为:CREATE OR REPLACE FUNCTION get_user_spcs(userId number,startDate varchar,endDate varchar)
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=20 then 20 else cs end) into icount from (
select user_id,to_char(create_date,'yyyy-mm-dd'),count(distinct video_id) cs
from t_video_review
where user_id=userId
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 user_id,to_char(create_date,'yyyy-mm-dd'));
RETURN nvl(icount,0);
END;
函数大意是统计从11月1号到31号的每个用户对视频的评论次数,
如果要改用left outer join 的方式,该怎么改?
像这个语句就用了这个方式select * from t_user a left ourer join (select count(1) as dz1,userid
from t_praise
where createtime >=to_date('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and createtime <=to_date('2016-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by userid) b on a.id=b.userid
where b.area_id = 201 and nvl(company_id,' ')<>' '
解决方案 »
- Message file 'oracle.net.mesg.Message'
- oracle9I如何自动定时备份
- 请大家帮忙写一下SQL
- ORA-04021和ORA-01410错误,请高手帮忙啊
- 开发oracle10登录存储过程,对用户名、密码等判断是否存在(游标)
- 动态sql 的引号问题?
- 关于数据库中存储图片的问题
- ORACLE中左外联结怎么写?
- 一个小问题,我就是不明白? 在线等
- Oracle8i有没有一个类似SQL Server Enterprise Manager那样的图形化的数据库管理工具?
- 存储过程执行出错,请大家帮忙看看哪里有问题
- 求助!可不可以把一个用户的表和视图复制到另一个用户?
然后再观察一下,表连接走的是NL,还是HASH JOIN