过去一周左右我一直在研究这个问题，但似乎无法找到解决方案。我正在尝试创建一个查询，在其中从各个表中获取数据，主要是按周(weekofyear)聚合日期。因此，我创建了一个日历表，如下所示；

   DATE,    YEAR, MONTH_OF_YEAR, WEEK_OF_YEAR,

2014-01-01  2014       1               1
2014-01-02  2014       1               1
2014-01-03  2014       1               1
2014-01-04  2014       1               1
2014-01-05  2014       1               1
2014-01-06  2014       1               2

数据会一直持续到任何时候。

所以，我汇总了所有这些表格和数据，并希望得到过去 10 周以及没有事件的几周的结果，我想要 0。

我编写这个查询是为了从 2 个表中获取数据，并将它们连接到日历表的 week_of_year 上(请不要折磨我，SQL 不是我的母语)。

select calendar.YEAR,
       calendar.WEEK_OF_YEAR,
       ifnull(count(data_transfer.id_data_transfer), 0) as DEVICE_TO_DEVICE,
       ifnull(count(device.tag_code), 0) as DEVICE_REGISTRATION
from calendar
left join data_transfer 
    on data_transfer.WEEK_NUMBER=calendar.WEEK_OF_YEAR -- wee number
        and data_transfer.year = calendar.YEAR -- need the year of week
        and data_transfer.id_customer = 1 --  specific customer
        and data_transfer.cloud_type is null --  specific type etc
        and date(data_transfer.TRANSFER_START_TIME) > date(date_sub(now(), INTERVAL 1 YEAR)) -- for the final week to be this week
left join device 
    on device.WEEK_NUMBER = calendar.WEEK_OF_YEAR       
        and device.year = calendar.YEAR
        and device.ID_CUSTOMER = 1
        and date(device.DEVICE_CREATE_DATE) > date(date_sub(now(), INTERVAL 1 YEAR)) -- for the final week to be this week
where date(calendar.date) > date(date_sub(now(),INTERVAL 1 YEAR)) -- for the final week to be this week
group by calendar.year, calendar.WEEK_OF_YEAR
order by calendar.year, calendar.WEEK_OF_YEAR;

查询返回多个 double ，我认为这可能是由于日期表有多个 week_of_year 条目，即 7 由于一周中的天数？

例如，当我使用 group_concat(special_field) 运行它时，我得到的结果如图所示。这就是我要解决的问题

YEAR, WEEK_OF_YEAR, DEVICE_TO_DEVICE, DEVICE_REGISTRATION, group_concat(data_transfer.id_data_transfer), id
2014    46  280 280 50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,51,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,52,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,53,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,54,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,55,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,56,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57,57

这就是我正在尝试解决的问题，任何与此相关的帮助将不胜感激。

最佳答案

是的，问题是您有多个记录具有相同的年份和年份值。

1. 创建一个日历表，其中表中只有一个年-周对，并使用它来连接您的日志数据。

2. 或者在查询中使用 select unique。

    select distinct calendar.YEAR,
       calendar.WEEK_OF_YEAR,
       ifnull(count(data_transfer.id_data_transfer), 0) as DEVICE_TO_DEVICE,
       ifnull(count(device.tag_code), 0) as DEVICE_REGISTRATIO
     from...

select c.YEAR,
       c.WEEK_OF_YEAR,
       ifnull(count(data_transfer.id_data_transfer), 0) as DEVICE_TO_DEVICE,
       ifnull(count(device.tag_code), 0) as DEVICE_REGISTRATION
from (select distinct YEAR, WEEK_OF_YEAR FROM calendar) c
left join data_transfer 
    on data_transfer.WEEK_NUMBER=c.WEEK_OF_YEAR -- wee number
        and data_transfer.year = c.YEAR -- need the year of week
        and data_transfer.id_customer = 1 --  specific customer
        and data_transfer.cloud_type is null --  specific type etc
        and date(data_transfer.TRANSFER_START_TIME) > date(date_sub(now(), INTERVAL 1 YEAR)) -- for the final week to be this week
left join device 
    on device.WEEK_NUMBER = c.WEEK_OF_YEAR       
        and device.year = c.YEAR
        and device.ID_CUSTOMER = 1
        and date(device.DEVICE_CREATE_DATE) > date(date_sub(now(), INTERVAL 1 YEAR)) -- for the final week to be this week
where date(calendar.date) > date(date_sub(now(),INTERVAL 1 YEAR)) -- for the final week to be this week
group by c.year, c.WEEK_OF_YEAR
order by c.year, c.WEEK_OF_YEAR;

关于Mysql，选择引用日期(weekofyear)的语句，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33654326/