复制MySQL查询的结果集

Question

我想根据表格日期的工作日平均取消特定查询的值，并在其他日子中复制。我知道这种解释令人困惑，请参见下面的示例。

Eg:- Table Structure - 
ID, Value, Date
Expected Result-
Avg(value), Weekname(day)
12.23    , Mon
12.23    , Tue
12.23    , Wed
12.23    , Thu
12.23    , Fri
34.56    , Sat
56.34    , Sun

ie，周一至周五的价值是相同的。

Current Result
Avg(value), Weekname(day)
12.23    , Mon
34.56    , Sat
56.34    , Sun

查询看起来像这样 -

select avg(value)
      ,daynameofweek(date) 
from table
group by (CASE WHEN  DAYOFWEEK(DATE) NOT BETWEEN 2 and 6 THEN DAYOFWEEK(DATE) END)

感谢您的帮助。

编辑 - 添加示例数据集

ID  Value   Date
1   2.500   2017-01-01
2   0.674   2017-01-02
3   2.743   2017-01-03
4   1.460   2017-01-04
5   1.457   2017-01-05
6   1.791   2017-01-06
7   1.896   2017-01-07
8   2.015   2017-01-08
9   2.224   2017-01-09
10  1.635   2017-01-10
11  1.100   2017-01-11
12  0.441   2017-01-12
13  0.809   2017-01-13
14  1.508   2017-01-14

预期结果

Avg(Value)  daynameofweek(date) 
1.625   Monday
1.625   Tuesday
1.625   Wednesday
1.625   Thursday
1.625   Friday
1.300   Saturday
1.702   Sunday

Answer 1

您表明您最终在周一至周五的'Mon'中最终出现了，但这是由DBMS任意选择的，您也可以最终以'Wed'的方式选择，因为这一天也在分组范围内。因此，首先在此处获得确定值。然后加入虚构的日子表：

select
  days.dayname,
  data.avg_value
from
(
  select 
    case when dayofweek(date) between 2 and 6 then 2 else dayofweek(date) end as day
    avg(value) as avg_value
  from table
  group by case when dayofweek(date) between 2 and 6 then 2 else dayofweek(date) end
) data
join
(
  select 1 as day, 'SUN' as dayname, 7 as sortkey union all
  select 2 as day, 'MON' as dayname, 1 as sortkey union all
  select 2 as day, 'TUE' as dayname, 2 as sortkey union all
  select 2 as day, 'WED' as dayname, 3 as sortkey union all
  select 2 as day, 'THU' as dayname, 4 as sortkey union all
  select 2 as day, 'FRI' as dayname, 5 as sortkey union all
  select 7 as day, 'SAT' as dayname, 6 as sortkey
) days on days.day = data.day
order by data.sortkey;

（如果我没记错的话，MySQL允许按别名名称（group by day）进行分组，这不是标准的。