How Does Oracle learn to break down complex issues?

// Problem
Table TEST:
ID name consumption date retirement sign (1-on-duty, 2-retired)
004 Zhang San 20 2010-05-11 1
007 Li Si 50 2010-06-23 1
002 Wang Wu 10 2010-06-23 2
004 zhangsan 100 2010-07-20 2
// Group by retirement sign. The consumption count and amount are counted. The result is:
Retirement sign consumption
1 1 50
2 3 130
// Note: Michael Jacob has retired since, and his previous post consumption is based on the number of retires and the consumption amount.
// Solution:
// Problem Analysis:
// If you only count on-duty and retired personnel, a simple query statement is provided:
Select retirement flag, count (*) cnt, sum (consumption) amount
From test
Group by retirement mark;
// However, a key issue here is that the same person's retirement logo varies in different time periods,
// That is, when I was on duty, I retired;
// When I encountered this problem, there may be no idea. For the same person, there may be different retires in different periods of time;
// In fact, with the foundation of the above query, we can solve this problem;
// All we need to do is change the retirement sign of all retired personnel in the table to 2;
// We need to use the embedded view in this way:
// Let's modify the retirement mark of a retired employee:
// The modification mentioned here is not an update table, but a flag:
With test (
Select '004 'id, 'zhang san' name, 20 amount, date' 2010-05-11 'pdate, 1 flag from dual union all
Select '007 ', 'Li si', 50, date '2017-06-23', 1 from dual union all
Select '002 ', 'wang wu', 10, date '2017-06-23', 2 from dual union all
Select '004 ', 'zhang san', 100, date '2017-07-20', 2 from dual)
Select t .*,
Case
When (select count (*)
From test
Where a. id = t. id
And a. flag = 2)> 0
Then 2
Else 1
End newflag
From test t
/
ID NAME AMOUNT PDATE FLAG NEWFLAG
------------------------------------------------
004 Zhang San 20 2010-05-11 1 2
007 Li Si 50 2010-06-23 1 1
002 Wang Wu 10 2010-06-23 2 2
004 zhangsan 100 2010-07-20 2 2
// In the above case, the pre-retirement mark of retired personnel is marked from 1 to 2;
// The when section in the lower layer uses a table self-join query:
Select a. id, count (*) cnt
From test a, test B
Where a. id = B. id
And a. flag = 2
Group by a. id
/
ID CNT
-------------
002 1
004 2
// This subquery is used to find the ID number of the retired employee,
// In the case statement, set the idnumber to 2 for the retired retirement sign,
// This is the newflag we see in the above query.
// It is then used as a new retirement sign for the primary query.
// Finally, let's see how to construct the simple query proposed at the beginning:
With test (
Select '004 'id, 'zhang san' name, 20 amount, date' 2010-05-11 'pdate, 1 flag from dual union all
Select '007 ', 'Li si', 50, date '2017-06-23', 1 from dual union all
Select '002 ', 'wang wu', 10, date '2017-06-23', 2 from dual union all
Select '004 ', 'zhang san', 100, date '2017-07-20', 2 from dual)
Select newflag flag, count (*) cnt, sum (amount) amount
From (select t .*,
Case
When (select count (*)
From test
Where a. id = t. id
And a. flag = 2)> 0
Then 2
Else 1
End newflag
From test t)
Group by newflag
/
FLAG CNT AMOUNT
------------------------------
1 1 50
2 3 130
// Summary:
// When you encounter a problem, you must analyze it carefully and break down the problem. That is, you can make things easier and easier,
// Finally, you can solve the big problem by combining these small issues.