mysql - 如何通过条件计算左连接的行数

Question

这是我的查询:

SELECT claims.id,
COUNT(CASE WHEN claims.sold_at BETWEEN (now() - INTERVAL '7.day') AND now() END FROM claims) week1
FROM users 
LEFT JOIN claims ON claims.sales_user_id = users.id
WHERE users.office_id = 2

我想做的是加入 claim 表，然后还可以计算在特定日期期间内售出的数量

此查询出现错误，但不确定如何正确修复/处理它？<​​/p>

Answer 1

试试这个:

select
        users.id, count(claims.id) as count
    from users
    left join claims on claims.sales_user_id = users.id
        where
            users.office_id = 2 and
            claims.sold_at between (current_timestamp - interval '7' day) and current_timestamp
        group by users.id;