我已经搜索了所有的答案数据库,老实说还没有找到我需要的东西。我有一个表,其中包含合同值和与每个值相关的日期。我的查询应该是总结过去6个月累计的合同价值,从当前日期开始,回顾3个月,始终考虑6个月的累计期:
SELECT
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 0 MONTH), INTERVAL 6 MONTH) AND (ContractCancelled <>1)) * ContractValue) AS Today,
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND (ContractCancelled <>1)) * ContractValue) AS OneMonthAgo,
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) AND (ContractCancelled <>1)) * ContractValue) AS TwoMonthsAgo,
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND (ContractCancelled <>1)) * ContractValue) AS ThreeMonthsAgo
FROM Contracts
查询工作正常,但我在单行中获得结果,每个值在不同的列中累积:
+----+---------+-------------+--------------+----------------+
| | Today | OneMonthAgo | TwoMonthsAgo | ThreeMonthsAgo |
+--------------+-------------+--------------+----------------+
| 1 | 3434005 | 3992877 | 4104565 | 3688412 |
+--------------+-------------+--------------+----------------+
我需要的是把这个查询变成一个图,要做到这一点,而不是列,累积的结果应该显示为行,或者像这样:
+---------------+--------------------------------------+
| LookingTo | AcumulatedContractValue (6 months) |
+---------------+--------------------------------------+
| Today | 3434005 |
+---------------+--------------------------------------+
| OneMonthAgo | 3992877 |
+---------------+--------------------------------------+
| TwoMonthsAgo | 4104565 |
+---------------+--------------------------------------+
|ThreeMonthsAgo | 3688412 |
+---------------+--------------------------------------+
如果它是在MS SQL,我相信PIVOT应该工作,但我不知道如何在MySQL中做到这一点。你能帮我一下吗?
我不明白你为什么需要这个但是你可以直接union:
SELECT 'Today' as `LookingTo`,
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 0 MONTH), INTERVAL 6 MONTH) AND (ContractCancelled <>1)) * ContractValue) as Acumulated
FROM Contracts
UNION ALL
SELECT 'OneMonthAgo',
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND (ContractCancelled <>1)) * ContractValue)
FROM Contracts
UNION ALL
SELECT 'TwoMonthsAgo',
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) AND (ContractCancelled <>1)) * ContractValue)
FROM Contracts
UNION ALL
SELECT 'ThreeMonthsAgo',
SUM((ContractDate > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), INTERVAL 6 MONTH) AND (ContractDate <= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND (ContractCancelled <>1)) * ContractValue)
FROM Contracts