MySQL如何显示逐月收益或亏损？

Question

我的问题涉及逐月的数据更改。我想要一个简单的(如果可能) SQL查询，它将显示人员，并按人员/月份或类似的分组的月份更改。重要的部分是显示得失。

例如，我有一个表：(简化日期)

id | Date      | Person | Sales
---|-----------|--------| -----
1  | 2010-9-01 | Alice  |  5
2  | 2010-8-01 | Alice  |  2

3  | 2010-9-01 | Peter  |  2
4  | 2010-8-01 | Peter  |  3

5  | 2010-9-01 | Bob    |  5
6  | 2010-8-01 | Bob    |  7

从这张表中，我希望得到这样的报告。

Month |  Person | Sales_total | Gain-Loss_from_Previous_month |
------  -------- ------------- -------------------------------
2010-9   Alice       7             +3
2010-9   Peter       5             -1
2010-9   Bob         12            -2

这可以作为一个纯MySQL查询来完成吗？O

Answer 1

您可能想要尝试如下所示：

SELECT     CONCAT(YEAR(s1.date), '-', MONTH(s1.date)) month, 
           s1.person, 
           s2.total_sales sales_total,
           s1.sales - s3.sales gain_loss
FROM       sales s1
JOIN       (
              SELECT person, SUM(sales) total_sales FROM sales GROUP BY person
           ) s2 ON (s2.person = s1.person)
LEFT JOIN  sales s3 ON 
           (s3.date = DATE_SUB(s1.date, INTERVAL 1 MONTH) AND s3.person = s1.person)
WHERE      s1.date = STR_TO_DATE(CONCAT('01-', MONTH(NOW()), '-', YEAR(NOW())), '%d-%m-%Y')
GROUP BY   s1.person, s1.date
ORDER BY   s1.person, s1.date;

s1.date = STR_TO_DATE(...)基本上将s1.date限制为当前月份和年份的WHERE子句部分。我有一种感觉，有一个更简洁的解决方案，但目前我想不出合适的替代方案。

测试用例：

CREATE TABLE sales (id int, date date, person varchar(40), sales int);

INSERT INTO SALES VALUES (1, '2010-9-01', 'Alice', 5);
INSERT INTO SALES VALUES (2, '2010-8-01', 'Alice', 2);
INSERT INTO SALES VALUES (3, '2010-9-01', 'Peter', 2);
INSERT INTO SALES VALUES (4, '2010-8-01', 'Peter', 3);
INSERT INTO SALES VALUES (5, '2010-9-01', 'Bob', 5);
INSERT INTO SALES VALUES (6, '2010-8-01', 'Bob', 7);

结果：

+--------+--------+-------------+-----------+
| month  | person | sales_total | gain_loss |
+--------+--------+-------------+-----------+
| 2010-9 | Alice  |           7 |         3 |
| 2010-9 | Bob    |          12 |        -2 |
| 2010-9 | Peter  |           5 |        -1 |
+--------+--------+-------------+-----------+
3 rows in set (0.00 sec)

Answer 2

如果您只想比较两个月，下面是一个相对简单的查询：

SELECT   '2010-9' month, 
         s1.person, 
         sum(s1.sales) sales_total,
         sum(s1.sales * (case when MONTH(s1.date) = 8 then -1 else 1 end)) gain_loss
FROM     sales s1
WHERE    s1.date >= STR_TO_DATE('01-08-2010', '%d-%m-%Y') AND
         s1.date < STR_TO_DATE('01-10-2010', '%d-%m-%Y')
GROUP BY s1.person