两张表，如何统计用户信息和总数？

select
    uid
        , sum(CASE when created_at between '2022-01-01' and '2022-01-01 23:59:59.999' THEN 1 else 0 end) one_day
    , sum(CASE when created_at between '2022-01-01' and '2022-01-07 23:59:59.999' THEN 1 else 0 end) one_week
from tb
where created_at between '2022-01-01' and '2022-01-07 23:59:59.999' -- 其他条件
group by uid

首先确定参数为当天与本周日期，假设今天为2023:02:17
解法一：
SELECT
u.id,
u. NAME,
IF (d.count, d.count, 0) AS ‘count () 当天的文章数量’,
IF (w.count, w.count, 0) AS ‘count () 本周的文章数量’
FROM
articles a
LEFT JOIN users u ON u.id = a.user_id
LEFT JOIN (
SELECT
user_id,
count() AS count
FROM
articles
WHERE
created_at LIKE ‘2023:02:17%’
GROUP BY
user_id
) d ON d.user_id = u.id
LEFT JOIN (
SELECT
user_id,
count() AS count
FROM
articles
WHERE
YEARWEEK(created_at, ‘%Y:%m:%d’) = YEARWEEK(now())
GROUP BY
user_id
) w ON w.user_id = u.id

解法二：
SELECT
u.id,
u. NAME,
sum(
IF (
LEFT (created_at, 10) = “2023:02:17”,
1,
0
)
) AS date_count,
sum(
IF (
YEARWEEK(created_at, ‘%Y:%m:%d’) = YEARWEEK(now()),
1,
0
)
) AS week_count
FROM
articles a
LEFT JOIN users u ON u.id = a.user_id
GROUP BY
u.id

推荐使用模型关联，聚合查询，加上检索条件即可

模型关联《Laravel 9 中文文档》

$posts = Post::withCount([
    'comments',
    'comments as pending_comments_count' => function (Builder $query) {
        $query->where('approved', false);
    },
])->get();

拿走试试 :smile:

SELECT user.username, COUNT(article.article_id) AS daily_count FROM user INNER JOIN article ON user.user_id = article.user_id WHERE article.publish_date >= CURDATE() GROUP BY user.user_id;

SELECT user.username, YEARWEEK(article.publish_date) AS yearweek, COUNT(article.article_id) AS weekly_count FROM user INNER JOIN article ON user.user_id = article.user_id GROUP BY user.user_id, YEARWEEK(article.publish_date);

在user模型中定义 ，按道理是一对多吧

代码的话按道理应该是这样