有人能指出我的sql查询哪里错了吗?当我获取数据时,它没有给出所需的SUM结果?
我试图计算每个并集的前5个VAR_MEASURE_1结果,对于第一个并集,我应该得到-7.00,但我得到4.00。
EDIT-这是我使用总和查询之前的数据-
VAR_MEASURE_1
16587 SBEN -14.000 0.000 2014-09-30 00:31:24
16288 SBEN 3.000 0.000 2014-09-30 00:52:46
16288 SBEN 3.000 0.000 2014-09-30 00:53:59
16006 SBEN 5.000 0.000 2014-09-30 01:16:36
15271 SBEN -4.000 0.000 2014-09-30 01:40:09
15786 SBEN -6.000 0.000 2014-09-29 00:15:02
16097 SBEN 2.000 0.000 2014-09-29 00:17:34
16097 SBEN 2.000 0.000 2014-09-29 00:19:25
15771 SBEN -9.000 0.000 2014-09-29 00:38:49
16155 SBEN 7.000 0.000 2014-09-29 01:13:26
15661 SBEN -9.000 0.000 2014-09-28 17:40:05
14425 SBEN 1.000 0.000 2014-09-28 17:49:09
14425 SBEN 1.000 0.000 2014-09-28 17:50:24
15657 SBEN -9.000 0.000 2014-09-28 18:02:09
15655 SBEN -14.000 0.000 2014-09-28 18:24:31
这是之后的
SUMA
15276 Sample 1 SBEN 4.000 2014-09-30 00:31:24
15855 Sample 2 SBEN -77.000 2014-09-29 02:40:56
15661 Sample 3 SBEN -109.000 2014-09-28 17:40:05
$sql = "(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-30 00:00:00' AND '2014-09-30 23:59:59' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN' LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-29 00:00:01' AND '2014-09-29 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-28 00:00:01' AND '2014-09-28 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-27 00:00:01' AND '2014-09-27 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-26 00:00:01' AND '2014-09-26 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5);";
您误解了SUM()
的工作方式。
SUM()
并不局限于LIMIT
——LIMIT
仅告诉sql您希望在结果中获得多少行。因此,在您的案例中,每个UNION
部分都有满足要求的所有行的总和,而不仅仅是前五行。
如果您想选择满足特定条件的表的前五行的总和,您应该像下面的示例中那样做。子查询只检索五行,然后外部查询能够将其相加。
需要明确的是,这是SQL,只检索UNIONed查询的第一部分的数据。你可以试着构建你的其他UNION
部件,比如:
SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-30 00:00:00'
AND '2014-09-30 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp
所以你的UNION应该是这样的:
(SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-30 00:00:00'
AND '2014-09-30 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp)
UNION
( SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-29 00:00:00'
AND '2014-09-29 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp)
UNION
( .... more sqls here if needed ... )
但我假设您希望按天获得结果,如果"每天前五行"不重要,您可以简单地选择按天分组的汇总结果(通过DATE(doe)
实现):
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, DATE(doe) as date_doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA
FROM LIVE_VARCOLL
WHERE CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
GROUP BY DATE(doe)
ORDER BY DATE(doe)