MySQL显示每月记录 [英] MySQL show records month wise

问题描述

我的数据库表中有一些记录.

I have some records in my Database table.

用户传递开始日期和结束日期，然后期望输出.如果用户传递了同一月份的开始日期和结束日期，那么它应该仅显示一个记录，但是如果结束日期延长了一个或多个月，则应该显示按月记录.

User passes the start and the end date and then expects an output. If a user passes the start and end date for same month then it should show only one record, but if the end date is extended one or more month then it should displays month wise record.

目前，我能够在执行该查询时收集总数据.

For now I am able to collect total data while following this query.

SELECT m.data_date_time
     , SUM(m.kwh) total_kwh
     , m.cust_id Customer_ID
     , m.msn
  FROM mdc_meters_data m 
 WHERE m.data_date_time >= '2020-04-01 00:00:00' 
   AND m.data_date_time <= '2020-05-31 00:00:00'

输出

Data_Date_Time      == total_kwh == Customer_ID == MSN
2020-04-01 11:44:13 || 95585     || 37010114707 || 7898985212

预期产量

由于月结束日期为05，因此它应显示两条记录，如下所示

As the month end date is 05 so it should show two records like below

Data_Date_Time      == total_kwh == Customer_ID == MSN
2020-04-01 11:44:13 || 50000     || 37010114707 || 7898985212
2020-05-01 10:25:05 || 45585     || 37010114707 || 7898985212

我曾尝试放下GroupBy data_date_time，但这并没有帮助我.

I have tried to put GroupBy data_date_time but it doesn't helped me out.

我该怎么做?

任何帮助将不胜感激.

推荐答案

您将必须以正确的方式使用GROUP BY.

You will have to use GROUP BY the correct manner.

SELECT DATE_FORMAT('m.`data_date_time`, '%Y-%m') AS Data_Date_Time, 
       SUM(m.`kwh`) AS total_kwh, 
       m.`cust_id` AS Customer_ID,
       m.`msn` AS MSN 
FROM `mdc_meters_data` m 
WHERE m.`data_date_time`>='2020-04-01 00:00:00' AND m.`data_date_time`<='2020-05-31 23:59:59'
GROUP BY Customer_ID, Data_Date_Time, MSN

现在上述查询的问题是您不需要按MSN

Now the problem with the above query is that you don't need to group your results by MSN

一种解决方法是在选择列表中未聚集的MSN列周围使用any_value函数.这只会从匹配列表中选择任何MSN值，并确保结果中仍然只显示2个条目.

One workaround is to use any_value function around your nonaggregated MSN columns in the select list. This will just pick any MSN value from the matching list and make sure you will still have only 2 entries shown in the results.

SELECT any_value(DATE_FORMAT('m.`data_date_time`, '%Y-%m')) AS Data_Date_Time, 
       SUM(m.`kwh`) AS total_kwh, 
       m.`cust_id` AS Customer_ID,
       any_value(m.`msn`)
FROM `mdc_meters_data` m 
WHERE m.`data_date_time`>='2020-04-01 00:00:00' AND m.`data_date_time`<='2020-05-31 23:59:59'
GROUP BY Customer_ID, Data_Date_Time

另外，通过将范围扩展到时间23:59:59来指定5月31日是如何包含在您的查询中的.

Also, note how the 31st of May has been included in your query by giving by extending the range to the time 23:59:59.

这篇关于MySQL显示每月记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！