mySQL：由不同的小组求和 [英] mySQL: sum by a different group-by

问题描述

我有一张表：
（用x5编辑）

I have a table: (Edited with x5)

_date(2014-03-05;2014-04-05)
_period(1,2,3,4,5,1,2,3,4,5,1,2; 1,2,3,4,5,1,2,3,4,5)
_id(x1,x1,x1,x1,x1,x2,x2,x2,x2,x2,x3,x3;x4,x4,x4,x4,x4,x5,x5,x5,x5,x5)
_reading(1,1,1,1,1,2,2,2,2,2,5,6;1,1,1,1,1,2,1,1,1,1)
_reading2 (5,5,5,5,5,4,4,4,4,4,7,7;1,1,1,1,1,1,1,1,1,1)

表格1的图像

我需要像下面这样的结果，消除所有没有的数据对于_id，整个周期为1到5，然后计算一天中_read的总和和_reading2的总和，用于每天的每个不同的_id：

i need a result like below which eliminates all data which doesn't have full period 1 to 5 for the _id, then calculates the sum of _reading for a day and sum of _reading2 for each distinct _id for a day:

_date(2014-03-05;2014-04-05)
sum_reading(15;11)
sum_reading2 (9;2)

表格2的图片< a>

image of the table2

推荐答案

测试：

Tested: SQL Fiddle

我使用子查询来消除_IDs，其中没有完整的5个阶段。
然后，我使用按日期分组和_ID以及sum和vs distinct来获得总数和总结不同的值。

I use a subquery to eliminate the _IDs which don't have a full 5 periods. I then use the group by date and _ID and sum vs sum distinct to get the sum total vs summing distinct values.

SELECT A._date, sum(_Reading) as _Reading, 
              sum(Distinct _Reading2) as _Reading2
FROM table A
INNER JOIN (SELECT _ID, _date
            FROM table 
            GROUP BY _ID, _date
            HAVING count(_Period) = 5) B
 on A._ID = B._ID
and A._Date = B._Date
GROUP BY A._Date

这仍然不能满足您的需求，因为读数和减少的顺序可能实际上是重要的是阅读2;不只是总结不同。但由于我对需求不清楚，而且这与所需的结果相匹配......这是我现在可以做的最好的选择。

This still may not meet your needs because order of readings and a reduction may actually be important in reading 2; not just summing distinct. But as I'm unclear as to the requirements, and this matches the desired result... it's the best I can for now.

更新X5 - 通过获取我们可以总结我认为可能解决问题的读数。

Update for X5 - By getting the sum in a distinct at the inner level we can then sum the readings which I think may address the issue.

SELECT A._date, sum(_Reading) as _Reading, 
              sum(B.I_reading2) as _Reading2
FROM table A
INNER JOIN (SELECT _ID, _date, Sum(Distinct _Reading2) as I_Reading2
            FROM table 
            GROUP BY _ID, _date
            HAVING count(_Period) = 5) B
 on A._ID = B._ID
and A._Date = B._Date
GROUP BY A._Date

这篇关于mySQL：由不同的小组求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！