MySQL -按客户端划分的当前收入与上月收入

Question

我想知道是否有人可以帮助我锻炼MySQL版本5.7.22脚本检索当前和上个月的收入在一个表按日期和AccountID分组

我有以下头从内部连接从其他表

• 日期(月的最后一天)
• 帐号名称

到目前为止我有什么

Select 
Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
CLIENT NAME,  
Sum(Subtotal) AS 'REVENUE'
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id

GROUP BY 
Last_Day(Invoice.Date),
Clients.Id
ORDER BY
Invoice.Date desc

我的期望

Select 

Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
CLIENT NAME,  
Sum(Subtotal) AS 'REVENUE'
Sum(Subtotal) AS 'PMREVENUE' <-- this to be previous month revenue record per client
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id

GROUP BY 
Last_Day(Invoice.Date),
Clients.Id
ORDER BY
Invoice.Date desc

预期结果

假设我一无所知注意:由于版本问题，我不能使用LAG或WITH提前谢谢你

Answer 1

如果您只关心当前月份和上个月，您可以这样做:

Select 
Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
`CLIENT NAME`,  
Sum(CASE WHEN Last_Day(Invoice.Date)=Last_Day(CURDATE()) THEN Subtotal ELSE 0 END) AS 'REVENUE'
Sum(CASE WHEN Last_Day(Invoice.Date)=Last_Day(DATE_ADD(DATE(), INTERVAL  -1 MONTH) THEN Subtotal ELSE 0 END) AS 'PMREVENUE' 
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id
GROUP BY 
Clients.Id

如果您的报表需要每个月返回一次，那么您需要这样做:

WITH MonthEnd AS (
Select 
Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
`CLIENT NAME`,  
Sum(Subtotal) AS 'REVENUE'
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id
GROUP BY 
Last_Day(Invoice.Date),
Clients.Id
)
SELECT CURRENT.DATE, 
CURRENT.ACCOUNTID, 
CURRENT.`CLIENT NAME`, 
CURRENT.REVENUE, 
PREVIOUS.REVENUE AS PMREVENUE
FROM MonthEnd AS CURRENT
JOIN MonthEnd AS PREVIOUS ON PREVIOUS.`DATE` = LAST_DAY(DATE_ADD(CURRENT.`DATE`, INTERVAL -1 MONTH)
AND CURRENT.ACCOUNTID=PREVIOUS.ACCOUNTID
ORDER BY CURRENT.`DATE` DESC

编辑:在没有WITH的情况下做这个只是用子查询替换对CTE的引用的问题:

SELECT CURRENT.DATE, 
CURRENT.ACCOUNTID, 
CURRENT.`CLIENT NAME`, 
CURRENT.REVENUE, 
PREVIOUS.REVENUE AS PMREVENUE
FROM (
Select 
Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
`CLIENT NAME`,  
Sum(Subtotal) AS 'REVENUE'
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id
GROUP BY 
Last_Day(Invoice.Date),
Clients.Id
) AS CURRENT
JOIN (
Select 
Last_Day(Invoice.Date) AS 'DATE', 
Client.Id as ACCOUNTID, 
`CLIENT NAME`,  
Sum(Subtotal) AS 'REVENUE'
FROM Invoice
INNER JOIN Clients ON Clients.Id = Invoice.Id
GROUP BY 
Last_Day(Invoice.Date),
Clients.Id
) AS PREVIOUS ON PREVIOUS.`DATE` = LAST_DAY(DATE_ADD(CURRENT.`DATE`, INTERVAL -1 MONTH))
AND CURRENT.ACCOUNTID=PREVIOUS.ACCOUNTID
ORDER BY CURRENT.`DATE` DESC