gpt4 book ai didi

php - 获取两个表的统计数据

转载 作者:行者123 更新时间:2023-11-29 10:39:53 25 4
gpt4 key购买 nike

两个表 - posts - 和 users,公共(public)列 - posts.user/users.id

我需要输出一些统计数据,即每个用户今天、过去 7-30-365 天有多少帖子以及总数。

下面的代码可以工作,但如果可能的话,我很乐意减少查询数量。

$stmt0 = $db->query("select * from users where role = 'mod' order by name asc");
$count0 = $stmt0->rowCount();
while($row0 = $stmt0->fetch()){
$stmt1 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) = date(now())");
$stmt2 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 7 day");
$stmt3 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 30 day");
$stmt4 = $db->query("select id from posts where user = " . $row0['id'] . " and date(date) > curdate() - interval 365 day");
$stmt5 = $db->query("select id from posts where user = " . $row0['id']);
}

$count1 = $stmt1->rowCount();
$count2 = $stmt2->rowCount();
... etc.

最佳答案

MySQL 查询:

SELECT  
user,
COUNT(DISTINCT CASE WHEN date = CURDATE() THEN id END) as countToday,
COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 7 DAY THEN id END) as count7,
COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 14 DAY THEN id END) as count14,
COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 30 DAY THEN id END) as count30,
COUNT(DISTINCT CASE WHEN date >= CURDATE() - INTERVAL 365 DAY THEN id END) as count365,
COUNT(*) as countAll
FROM posts
GROUP BY posts.user

使用 PHP 访问:

$res = $db->query($query); // set $query to my query
while($row = $res>fetch()){ // loop the result
// access your row here
echo $row["user"] // $row["countToday"] ...
}

关于php - 获取两个表的统计数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45691700/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com