当周盘点记录

Question

我的数据库表目前有一个名为date的unix时间戳列。我正在尝试获取当前周的信息，例如，当前周的每天计数

星期一: 21星期二:0星期三:3星期四:8星期五:0星期六:0星期日:0

我知道如何获取过去7天的信息，但是我只需要显示当前周的信息，所以如果今天是周一，那么周二将不会显示任何数据，因为周二还没有到来。

如有任何建议，我们将不胜感激。

Answer 1

这应该是可行的：

SQL

SELECT COUNT(*) FROM mytable WHERE YEARWEEK(reg_date) = YEARWEEK(CURRENT_TIMESTAMP)

Answer 2

试试这个；)

SELECT dayInWeek, SUM(cnt) AS cnt
FROM (
    SELECT
        DATE_FORMAT(dateline, '%a') AS dayInWeek,
        COUNT(1) AS cnt,
        WEEKDAY(dateline) AS idx
    FROM registrations
    WHERE WEEK(dateline, 1) = WEEK(NOW(), 1) GROUP BY WEEKDAY(dateline)
    UNION SELECT 'Mon' AS dayInWeek, 0 AS cnt, 0 AS idx
    UNION SELECT 'Tue' AS dayInWeek, 0 AS cnt, 1 AS idx
    UNION SELECT 'Wed' AS dayInWeek, 0 AS cnt, 2 AS idx
    UNION SELECT 'Thu' AS dayInWeek, 0 AS cnt, 3 AS idx
    UNION SELECT 'Fri' AS dayInWeek, 0 AS cnt, 4 AS idx
    UNION SELECT 'Sat' AS dayInWeek, 0 AS cnt, 5 AS idx
    UNION SELECT 'Sun' AS dayInWeek, 0 AS cnt, 6 AS idx) TMP
GROUP BY dayInWeek
ORDER BY idx