2. Only the zl when there are changes to the data, if it does not change the day does not record
3. The content of the table example:
4. The date city area cl ml zl
5. The cc1 2018-05-01 a11 100, 99-1
6. The cc1 2018-05-02 200, 205, 5 a12
7. The 2018-05-02 cc2 a21 50 100
8. The 2018-05-03 cc1 a11 99, 51, 140
9. Should check each area within a certain time range of the number of initial and final quantity, example: check the 2018-05-01-2018-05-01, cc1 - a11 the beginning balance is 100. The final is 140, cc1 - a12 is the beginning of 200, the final is 205, cc2 - a21 beginning is 50, the final is 100
10. Find two ways to look up at the beginning and the end of the semester: one is to take the minimum date and maximum date's date, according to the date for value; Second, since the correlation, because the data volume to 20 million at the query speed is slow, look for optimization
11. Online, try so hard, thank you!
CodePudding user response:
The SELECT city, area,(select a1. Cl from a as a1 where a1. Date=min (a. d. ate) and a1. City=a.c ity and a1. Area=a.a rea) as cl,
(select a2. Ml from a as a2 where a2. Date=Max (a. d. ate) and a2. City=a.c ity and a2. The area=a.a rea) as ml
FROM where a date between '2018-05-01' AND '2018-05-03 s' group by city, area
I don't know how to performance?