按月分组查询

Question

我对使用group by month的查询有问题。此查询每月返回total_revenue。但是如果一年中的月份不包含任何数据，那么total_revnue就会不必要地增加。

SELECT COUNT(CT.cumTxnReportId),
   CT.cumTxnReportId,
   CT.ticketNum,
   DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y'),
   sum(netAmount) AS total_revenue,
   D.name,
   HOUR(CT.entranceDateTimeUtc) AS entryHour,
   HOUR(CT.exitDateTimeUtc) AS exitHour,
   CT.entranceDateTimeUtc,
   CT.exitDateTimeUtc,
   CT.netAmount AS netAmount,
   CT.grossAmount,
   CT.discountAmount,
   CT.rate,
   CT.txnType,
   CT.ticketType,
   CT.txnNum,
   CT.numDiscounts
FROM Parkloco.ParkingArea PA
JOIN IParcPro.Device D ON PA.id = D.parkingAreaId
JOIN Parkloco.RateCard RC ON PA.id = RC.parkingAreaId
JOIN IParcPro.CumTxn CT ON D.id = CT.deviceId
WHERE PA.uuid = '27d842c1-7057-11e6-a0eb-1245b0d35d23'
  AND (CT.txnType = 'Allowed'
       OR CT.txnType = 'Add'
       OR CT.txnType = 'Normal'
       OR CT.txnType = 'Offline'
       OR CT.txnType = 'Repay')
  AND ((CT.entranceDateTimeUtc >= '2016-08-01 00:00:00'
        AND CT.exitDateTimeUtc <= '2017-04-31 23:59:59'))
  AND (RC.state = 'active'
       OR RC.state = 'archived')
  AND RC.fromDateTimeUtc <= '2017-04-31 23:59:59'
  AND (RC.thruDateTimeUtc IS NULL
       OR RC.thruDateTimeUtc >= '2016-08-01 00:00:00')
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) >= '0' * 60)
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) < '1441' * 60)
  AND CT.numDiscounts=0
  AND CT.ticketNum !=0
GROUP BY DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y')

​

​

但是，当我增加月度范围时，我在total_revenue中得到了不必要的增量

SELECT COUNT(CT.cumTxnReportId),
   CT.cumTxnReportId,
   CT.ticketNum,
   DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y'),
   sum(netAmount) AS total_revenue,
   D.name,
   HOUR(CT.entranceDateTimeUtc) AS entryHour,
   HOUR(CT.exitDateTimeUtc) AS exitHour,
   CT.entranceDateTimeUtc,
   CT.exitDateTimeUtc,
   CT.netAmount AS netAmount,
   CT.grossAmount,
   CT.discountAmount,
   CT.rate,
   CT.txnType,
   CT.ticketType,
   CT.txnNum,
   CT.numDiscounts
FROM Parkloco.ParkingArea PA
JOIN IParcPro.Device D ON PA.id = D.parkingAreaId
JOIN Parkloco.RateCard RC ON PA.id = RC.parkingAreaId
JOIN IParcPro.CumTxn CT ON D.id = CT.deviceId
WHERE PA.uuid = '27d842c1-7057-11e6-a0eb-1245b0d35d23'
  AND (CT.txnType = 'Allowed'
       OR CT.txnType = 'Add'
       OR CT.txnType = 'Normal'
       OR CT.txnType = 'Offline'
       OR CT.txnType = 'Repay')
  AND ((CT.entranceDateTimeUtc >= '2016-08-01 00:00:00'
        AND CT.exitDateTimeUtc <= '2017-07-31 23:59:59'))
  AND (RC.state = 'active'
       OR RC.state = 'archived')
  AND RC.fromDateTimeUtc <= '2017-07-31 23:59:59'
  AND (RC.thruDateTimeUtc IS NULL
       OR RC.thruDateTimeUtc >= '2016-08-01 00:00:00')
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) >= '0' * 60)
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) < '1441' * 60)
  AND CT.numDiscounts=0
  AND CT.ticketNum !=0
GROUP BY DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y')

输出如下：

​

有人能在这方面帮我吗？如果你能让我知道的话，提前谢谢你。

Answer 1

尽管MySQL允许这种奇怪的group by规则，但在我看来，您应该避免使用它。我通常会解释，所有select clause非聚合字段都应该出现在group by子句中：

select a,b,c, sum(z)
from t
group by a,b,c

vs

select a,b,c, sum(z)
from t
group by a   #<--- MySQL allow this!

那么，如果b和c不在group by中，MySQL如何确定要选择的正确字段？Like this on <5.6

服务器可以自由地从每个组中选择任何值，因此，除非它们相同，否则选择的值是不确定的。

在我看来，在中，您的查询是没有意义的: Look entryHour total_revenue。一个是一个条目，另一个是整个月。

我想您应该重新考虑hole sql语句。因为这个的结果是不连贯的。

另外，请记住这不是“代码修订服务”。请阅读how to create a Minimal, Complete, and Verifiable example，以便您的问题也能帮助其他用户。