SQL optimization problems, such as online, I want to repeat the query condition points out, just hav-CodePudding

Select (select shortname from ldcom where comcode=v.com code) | | '____,

(select codename
The from ldcode
Where codetype='salechnl'
And code=v.c ode),
The sum (cbcount),
NVL (sum (cbsumprem), 0),
The sum (wtcount),
NVL (sum (wtsumprem), 0),
The sum (ctcount),
NVL (sum (ctsumprem), 0),
The sum (xtcount),
NVL (sum (xtsumprem), 0)
The from (select k.com code comcode,
Keith hortname,
, dc ode CODE,
, dc odename,
(select count (distinct l.c ontno)
The from v_temp_bqcont l
Where l.o ptype='CB'
And "anagecom like k.com code | | '%'
And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)
And l.s alechnl=, dc cbcount ODE),
(select SUM (" oney)
The from v_temp_bqcont l
Where l.o ptype='CB'
And "anagecom like k.com code | | '%'

And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)
And l.s alechnl=, dc cbsumprem ODE),
(select count (DISTINCT l.c ontno)
The from v_temp_bqcont l
Where l.o ptype='WT'

And "anagecom like k.com code | | '%'
And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc wtcount ODE),
1 * (select sum (" oney)
The from v_temp_bqcont l
Where l.o ptype='WT'
And "anagecom like k.com code | | '%'
And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc wtsumprem ODE),
(select count (DISTINCT l.c ontno)
The from v_temp_bqcont l
Where l.o ptype='CT'
And "anagecom like k.com code | | '%'

And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc ctcount ODE),
1 * (select sum (" oney)
The from v_temp_bqcont l
Where l.o ptype='CT'
And "anagecom like k.com code | | '%'

And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc ctsumprem ODE),
(select COUNT (DISTINCT l.c ontno)
The from v_temp_bqcont l
Where l.o ptype IN (' XT ', 'YT')
And "anagecom like k.com code | | '%'

And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc xtcount ODE),
1 * (select sum (" oney)
The from v_temp_bqcont l
Where l.o ptype IN (' XT ', 'YT')
And "anagecom like k.com code | | '%'

And opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)

And l.s alechnl=, dc ODE) xtsumprem
The from ldcom k, ldcode d
Where, dc odetype='salechnl'
And k.com code not like '8600%'

And k.com code like '86030201%'
And, dc ode='03'
And LENGTH code (k.com)=8
The order by k.com code,, dc ode) v
Group by a ROLLUP (v.com code, v.c ode)

CodePudding user response:

First of all: the innermost)
The select l.o ptype, "anagecom, l.s alechnl, COUNT (DISTINCT l.c ontno) cc, sum (" oney) mm
The from v_temp_bqcont l
Where opdate between to_date (' 2017-01-01 ', '- dd yyyy - mm) and to_date (' 2017-07-27', '- dd yyyy - mm)
Group by l.o ptype, "anagecom, l.s alechnl
Second floor: the above table and ldcom k, ldcode correlation value according to the condition;
The third layer: according to the v.com code, v.c grouping the ode to the final result

Another: try not to order by in the middle tier, it will add additional overhead

CodePudding user response:

I drop a darling you this query column appeared much subquery,
The query column subquery use associated query value according to the condition,
Remove the your order by the subquery, save money, but also meaningless overhead