mysql - 如何在MySQL中选择与MAX值列相关的DATE

Question

我有一张 table tbl带柱date sales

我执行这个 MySQL 查询:

SELECT DAY(date) as DAY, count(sales) as sales_count
FROM tbl

我得到这个结果:

DAY      sales_count
1        224
2        361
3        155
4        281
.......
30       416
31       332

现在，我找到了 DAY在WEEK当销售额为MAX时通过这样做:

SELECT DAY(date) as DAY, WEEK(date) as WEEK, max(count(sales)) as MAX_SALES, 
FROM tbl
GROUP BY WEEK(date)

我明白了:

DAY    WEEK      MAX_SALES
1      1         324
8      2         461
15     3         255
22     4         481
.......

WEEK和MAX_SALES是正确的，但是 DAY值好像是第一个DAY WEEK的而不是 DAY MAX_SALES的发生。我可以确认这是因为我们有 GROUP BY WEEK这就是DAY正在获取与 WEEK 相关的值。我们不能按MAX()分组

我如何拥有 DAY值反射(reflect)MAX_SALES日期在 WEEK ？

Answer 1

您可以尝试使用子查询。

SELECT weekDay,
       DAY,
       MAX(sales_count)
FROM (
    SELECT DAY(date) as DAY, 
           Week(date) as weekDay
           count(sales) as sales_count
    FROM tbl
    GROUP BY 
        DAY(date),
        Week(date)      
) t1
group by 
       weekDay,
       DAY