The select get_user_spcs (b.i d, '2016-11-01', '2016-11-30') SPCS from
T_user b
Where b.a rea_id=201
And company_id is not null this function query efficiency is very bad
Get_user_spcs function content as follows:

The CREATE OR REPLACE FUNCTION get_user_spcs (userId number, startDate varchar, endDate varchar)
The RETURN number IS
Icount number;
The BEGIN
Select sum (case when cs>=20 then 20 else cs end) into icount the from (
The select user_id, to_char (create_date '- dd yyyy - mm), count (distinct video_id) cs
The from t_video_review
Where user_id=userId
And (startDate is null or create_date & gt;=to_date (startDate, 'yyyy - mm - dd))
And (endDate is null or create_date & lt;=to_date (endDate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss'))
Group by user_id, to_char (create_date '- dd yyyy - mm));
RETURN NVL (icount, 0);
The END;
Function is the statistics from November 1 to 31 each user comments on the video frequency,
If you want to switch to the left outer join, how to change?
As this statement is in the way the select * from t_user a left ourer join (select count (1) as dz1, userid
The from t_praise
Where createtime & gt;=to_date (' 2016-11-01 00:00:00 ', '- dd yyyy - mm hh24: mi: ss')
And createtime & lt; 23:59:59=to_date (' 2016-11-30 ', '- dd yyyy - mm hh24: mi: ss')
Group by userid) on Anderson, d=b b.u serid
Where b.a rea_id=201 and NVL (company_id, ' ') & lt;> ' '

CodePudding user response:

Pray god guide!!!!!

CodePudding user response:

Or try into the union

CodePudding user response:

There is an index on createtime?
Then look at the table joins to move is NL, or HASH JOIN