SELECT KL. Stores code, KL. Date, KL. RS,
HY, the cumulative number of the FROM
(SELECT s. stores code, TO_CHAR (s. sales date, 'yyyy - mm) date, the COUNT (DISTINCT S.L (SH) RS FROM membership sales list S
Where s. sales date BETWEEN '01 - July 18' AND '30 - July 18' GROUP BY s. stores code, TO_CHAR (s. sales date, 'yyyy - mm)) KL
LEFT the JOIN
(
The SELECT stores code, date,
The SUM (number) OVER (PARTITION BY store code ORDER BY date) the cumulative number of
The FROM
(
SELECT stores code, TO_CHAR (handle card date, 'yyyy - mm) date,
COUNT (case when disabled sign=1 then 0 else 1 end) number
The FROM the member table WHERE do card date & lt;=TO_DATE (' 2018-07-31 ', '- DD YYYY - MM)
Group by its code, TO_CHAR (handle card date, 'yyyy - mm))
HY)
ON KL. Code=HY store. Store code AND HY.
date=KL.
After the operation found because B mall mismanagement, a membership card is not done in July 2018, the total number of members in July 2017 in 0, this kind of situation to be accumulated membership number, B is the number of stores in June 2017, has accumulated, and how to write SQL
CodePudding user response:
The building Lord gives test data, and your calculation rules and the expected results,