主页 / dba / 问题 / 164598
Accepted
tachomi
Asked: 2017-02-17 13:42:11 +0800 CST

按周分组但显示更具可读性的结果？

  • 772

我有这个查询：

SELECT YEAR(datetime) AS Year, WEEK(datetime) AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week;

结果是：

+------+------+-------+
| Year | Week | total |
+------+------+-------+
| 2016 |   49 |    14 |
| 2016 |   50 |    20 |
| 2016 |   51 |    19 |
| 2016 |   52 |    15 |
| 2017 |    1 |    13 |
| 2017 |    2 |    14 |
| 2017 |    3 |    12 |
| 2017 |    4 |    12 |
| 2017 |    5 |     5

但我想知道如何以“更美丽”的方式展示这一周，例如：

+------+----------+-------+
| Year |    Week  | total |
+------+----------+-------+
| 2016 |   Dec 05 |    14 |
| 2016 |   Dec 12 |    20 |
| 2016 |   Dec 19 |    19 |
| 2016 |   Dec 26 |    15 |
| 2017 |   Jan 02 |    13 |
| 2017 |   Jan 09 |    14 |
| 2017 |   Jan 16 |    12 |
| 2017 |   Jan 23 |    12 |
| 2017 |   Jan 30 |     5 |
+------+----------+-------+
mysql optimization

4 个回答

  1. Best Answer

    您可以使用 DATE_FORMAT 函数完成此操作，

    SELECT YEAR(datetime) AS Year, DATE_FORMAT(datetime, '%b %e') AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week;
    • 6

  2. 接受的解决方案对我不起作用，因为它不是每周分组，但它可以在同一周内获得行，例如：

    year, week, total
2021, Mar 23, 1
2021, Mar 24, 2

    使用子查询似乎有效：

    SELECT 
    YEAR(ADDDATE(your_datetime, INTERVAL 1-DAYOFWEEK(your_datetime) DAY)) year,
    DATE_FORMAT(ADDDATE(your_datetime, INTERVAL 1-DAYOFWEEK(your_datetime) DAY), '%b %e') week,
    total
FROM
    (SELECT 
        YEAR(your_datetime) AS year,
            WEEK(your_datetime) AS week,
            COUNT(*) AS total,
            your_datetime
    FROM
        your_table
    GROUP BY `your_datetime`) AS sub;
    • 1

  3. 要按周分组并显示一周的第一天，您可以按照所示进行。从您存储的日期中减去星期几，然后根据需要格式化日期。然后，您可以按新的“日期”值分组，瞧！

    SELECT DATE_FORMAT(ADDDATE(buy.date_created, INTERVAL -DAYOFWEEK(buy.date_created) DAY),"%Y-%m-%d") as week, COUNT(*) AS total FROM buy GROUP BY week;

    结果

    +------------+-------+
|week        | total |
+------------+-------+
| 2016-12-05 |    14 |
| 2016-12-12 |    20 |
| 2016-12-19 |    19 |
| 2016-12-26 |    15 |
| 2017-01-02 |    13 |
| 2017-01-09 |    14 |
| 2017-01-16 |    12 |
| 2017-01-23 |    12 |
| 2017-01-30 |     5 |
+------------+-------+
    • 1 
  4. SELECT YEAR(buy.date_created) AS Year, WEEK(buy.date_created) as Week, DATE_FORMAT(buy.date_created, '%b %e') AS WeekString, COUNT(*) AS total
FROM buy
GROUP BY Year, Week
ORDER BY Year, week;

    我认为这是最好的建议。

    • -1

相关问题