此查询返回您在问题中要求的行:

This query returns the rows that you are asking for in the question:

SELECT s.*, p.plan_interval, p.price,
       (CASE WHEN p.plan_interval = 'month' 
             THEN p.price * 0.01 
             ELSE (p.price * 0.01)/12
        END) AS mrr
FROM subscriptions s JOIN
     plans p
     ON p.id = s.plan_id
WHERE YEAR(s.start_date) = 2018 AND 
      s.start_date = (SELECT MAX(s2.start_date)
                      FROM subscriptions s2
                      WHERE s2.account_id = s.account_id AND
                            EXTRACT(YEAR_MONTH FROM s2.start_date) = EXTRACT(YEAR_MONTH FROM s.start_date)
                     )
ORDER BY s.start_date ASC;

这使用子查询来获取每个月订阅的最新记录.

This uses a subquery to get the most recent record for a subscription for each month.

然后，您可以随意聚合.

You can then aggregate this however you wish.

关于查询的说明:

• 表别名使查询更易于编写和阅读.
• 子查询使用了 EXTRACT() 的方便的 YEAR_MONTH 选项，因此它可以处理年份和月份.
• 对于 -1 和 1 之间的数字常量，我总是在前面加上 0，所以 0.12 而不是 .12.如果发现这样会使小数点更明显.

• Table aliases make the query easier to write and to read.
• The subquery uses the handy YEAR_MONTH option of EXTRACT(), so it handles both years and months.
• For numeric constants between -1 and 1, I always prepend with a 0, so 0.12 rather than .12. If find that this makes the decimal point more obvious.