php - Mysql计数并拆分为日、周、年以在同一查询中进行统计

Question

我想获取某件事的统计数据。

我正在尝试统计今天、本周、本月的情况。

我的查询:

"SELECT COUNT(id) FROM images i WHERE i.user_id = 3 GROUP BY DAY(i.created_at), WEEK(i.created_at), MONTH(i.created_at)"

输出:

array(7) {
  [0]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
  [1]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
  [2]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "5"
  }
  [3]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "5"
  }
  [4]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "7"
  }
  [5]=>
  array(1) {
    ["COUNT(id)"]=>
    string(2) "17"
  }
  [6]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
}

但我想分开:

day = 5,
week = 15,
month = 67

但是如何呢？

<小时/>

完整的工作查询:

SELECT
                COUNT(i.id) AS `all`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id AND WEEK(created_at) = WEEK(NOW())
                )
                AS `week`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id AND MONTH(created_at) = MONTH(NOW())
                )
                AS `month`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id  AND DAY(created_at) = DAY(NOW())
                )
                AS `day`
            FROM
                images i
            WHERE
                i.user_id = " . $user->getId()

Answer 1

如果我没理解你的意思，这应该就是你要找的

SELECT Count(*)                         AS day, 
       (SELECT Count(*) 
        FROM   images 
        WHERE  user_id = i.user_id 
               AND Week(i.created_at) = Week(Now()) 
        GROUP  BY Week(i.created_at))  AS week, 
       (SELECT Count(*) 
        FROM   images 
        WHERE  user_id = i.user_id 
               AND Month(i.created_at) = Month(Now()) 
        GROUP  BY Month(i.created_at)) AS month 
FROM   images i 
WHERE  i.user_id = 3 
       AND Day(i.created_at) = Day(Now()) 
GROUP  BY Day(i.created_at)