计算当前周的记录

I currently have my database table that has a unix timestamp column called date. I'm trying to grab the information for the current week counted per day for the current week for example

Mon: 21 Tue: 0 Wed: 3 Thur: 8 Fri: 0 Sat: 0 Sun: 0

I know how to get information for the last 7 days however I need this to show for the current week only so if today is monday then tuesday will show no data as tuesday has not come around yet.

Any pointers would be appreciated.

This should work :

SQL

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

Try this;)

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