php - MySQL 具有多个日期范围

Question

我当前的查询:

    Select `userSubUnitsID`, `userSubUnits`.`userID`, CONCAT(`u`.`fname`,' ',`u`.`lname`) AS `full`, `pID`, SUM(`quantComp`) AS `total`
    From `userSubUnits`
    JOIN `u` ON `u`.`userID` = `userSubUnits`.`userID`
    GROUP BY `userID`

我需要的是在指定日期之前得到这个。所以它看起来更像这样。

    +-----+------------+----------+-------------+
    |user |current week|prev week |two weeks ago|
    +-----+------------+----------+-------------+
    |John |         564|       354|          687|
    +-----+------------+----------+-------------+
    |Paul |         451|       328|          845|
    +-----+------------+----------+-------------+
    etc

这可以通过单个查询实现吗？我知道如何在本周限制它，但不知道如何在前几周添加。

仅供引用，每周周日午夜结束。提前致谢

Answer 1

SELECT user, SUM(WEEKOFYEAR(datefield) = WEEKOFYEAR(now()) AS current_week,
    SUM(WEEKOFYEAR(datefield) = WEEKOFYEAR(now() - INTERVAL 1 WEEK)) AS last_week,
    SUM(WEEKOFYEAR(datefield) = WEEKOFYEAR(now() - INTERVAL 2 WEEK)) AS two_weeks_ago
FROM ...
GROUP BY WEEKOFYEAR(now())

丑陋，但应该可以解决问题。请注意，如果您的日期跨越一年边界，这将会失败。为了解决这个问题，您必须添加一些额外的基于年份的逻辑。

同样，您最好在客户端进行这种转换。扩展是很难看的，如果你需要开始做的事情超过几周，那么很快就会变得非常低效，例如:

SELECT user, YEAR(datefield), WEEKOFYEAR(datefield), COUNT(*)
FROM ...
GROUP BY YEAR(datefield), WEEKOFYEAR(datefield)