该表是我的真实表的简单示例，其中包含出口商，产品，标记，托盘，数量等。
但是我认为这张桌子最容易知道我的麻烦。
+----+--------+----------+----------+----------+------------+
| Id | Seller | Products | Marks | Quantity | Date |
+----+--------+----------+----------+----------+------------+
| 01 | John | Watch | Apple | 5 | 2017-01-01 |
| 02 | Peter | Mouse | Apple | 21 | 2017-01-01 |
| 02 | Peter | Mouse | Logitech | 10 | 2017-01-02 |
| 01 | John | Mouse | Logitech | 15 | 2017-01-03 |
| 02 | Peter | Keyboard | Logitech | 11 | 2017-01-05 |
| 01 | John | Keyboard | Apple | 10 | 2017-01-06 |
| 02 | Peter | Keyboard | Apple | 14 | 2017-01-06 |
| 01 | John | Keyboard | Logitech | 12 | 2017-01-06 |
| 01 | John | Keyboard | Apple | 17 | 2017-01-07 |
| 01 | John | Mouse | Logitech | 13 | 2017-01-08 |
| 01 | John | Watch | Apple | 6 | 2017-01-09 |
| . | . | . | . | . | . |
| 01 | Peter | Mouse | Logitech | 5 | 2018-01-01 |
| 02 | Steve | Keyboard | Apple | 7 | 2018-01-03 |
| 02 | Peter | Watch | Apple | 8 | 2018-01-03 |
| 01 | John | Mouse | Logitech | 6 | 2018-01-04 |
| 02 | Peter | Watch | Samsung | 9 | 2018-01-04 |
| 01 | John | Keyboard | Apple | 18 | 2018-01-08 |
| 01 | John | Watch | Apple | 7 | 2018-01-08 |
| 02 | Peter | Phone | Xiaomi | 19 | 2018-01-08 |
| 01 | John | Keyboard | Logitech | 15 | 2018-01-06 |
| 01 | Peter | TV | Samsung | 20 | 2018-01-07 |
| 01 | Steve | Display | LG | 11 | 2018-01-08 |
| 01 | John | Display | Samsung | 14 | 2018-01-08 |
| . | . | . | . | . | . |
+----+--------+----------+----------+----------+------------+
我需要在同一张表中比较两个期间（例如2018年1月与2017年1月），以使卖方知道销售结果。
这样的...
+----+--------+----------+----------+------------+------------+-----------+
| Id | Seller | Products | Marks | Total 2017 | Total 2018 | Diference |
+----+--------+----------+----------+------------+------------+-----------+
| 01 | John | Watch | Apple | 11 | 7 | -4 |
| 01 | John | Mouse | Logitech | 28 | 6 | -22 |
| 01 | John | Keyboard | Apple | 17 | 18 | 1 |
| 01 | John | Keyboard | Logitech | 12 | 15 | 3 |
| 01 | John | Display | Samsung | 0 | 14 | 14 |
| 02 | Peter | Mouse | Apple | 21 | 0 | -21 |
| 02 | Peter | Mouse | Logitech | 10 | 5 | -5 |
| 02 | Peter | Keyboard | Logitech | 11 | 0 | -11 |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
+----+--------+----------+----------+------------+------------+-----------+
我已经尝试使用这些查询，但是我得到了重复的值，并且卖方显示不正确。
SELECT *, (Q1 - Q2) AS Diference
FROM
(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A
INNER JOIN
(SELECT Id AS Id2, Seller, Products, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B
ON A.Id = B.Id2
SELECT *, (Q1 - Q2) AS Diference,
(CASE
WHEN Id IS NULL THEN Id2
ELSE Id
END) AS Id3,
(CASE
WHEN Id IS NULL THEN Seller2
ELSE Seller
END) AS Seller3
FROM
(SELECT Id, Seller, Products, Marks, SUM(Quantity) AS Q1
WHERE Date >= '2018-01-01' AND Date <= '2018-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS A
INNER JOIN
(SELECT Id AS Id2, Seller AS Seller2, Products AS Products2, Marks, SUM(Quantity) AS Q2
WHERE Date >= '2017-01-01' AND Date <= '2017-01-31' AND Seller IS NOT NULL
GROUP BY Id, Marks, Products) AS B
ON A.Id = B.Id2
一些帮助将不胜感激。
提前致谢。
最佳答案
只需使用条件聚合：
select Id, Seller, Products, Marks,
sum(case when year(date) = 2017 then quantity end) as quantity_2017,
sum(case when year(date) = 2018 then quantity end) as quantity_2018,
(sum(case when year(date) = 2018 then quantity end) -
sum(case when year(date) = 2017 then quantity end)
) as diff
from realtable
group by Id, Seller, Products, Marks;
关于java - SQL比较表中的两个期间并获取销售结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54225445/