mysql - SQL中如何使用IF THEN，具体场景

Question

我当前的问题是尝试构建一个查询，该查询给定条件，它给我一个特定的计数。

我需要提取在我们的网站上销售过商品但目前没有列出商品的用户列表。结果应该是仅拥有当前未发布的项目的用户列表。

我的查询如下:

select `products`.`id` as 'product id', `users`.`id` as 'user id', 
`users`.`full_name` as 'user full name',`users`.`email` as 'user email'
(CASE WHEN `products`.`is_published` = 1
    THEN count(`products`.`ìs_published`) = 0
    ELSE END )
from `users`,`products`
and `users`.`is_active` = 1
and `products`.`user_id` = `users`.`id`
group by `users`.`id`
order by 'user id' asc;

就上下文而言，如果 products.is_published = 1 则意味着用户有一个正在运行的商品，如果它 = 0，则意味着该商品尚未发布，因此卖家处于非事件状态。但是，在某些情况下，卖家可能拥有过去销售过的商品以及正在出售的商品，因此即使 products.is_published = 0，它们也会显示，因为他们两者都有。

任何帮助将不胜感激。

Answer 1

您可以在子查询中对用户的项目进行计数，如下所示:

select users.id as 'user id'
, users.full_name as 'user full name'
, users.email as 'user email'
, (select count(1) from products where is_published = 1 and user_id = users.id) as 'count of published'
from users
Where is_active = 1
order by 'user id' asc;

如果您只需要在用户发布产品时进行计数，请尝试以下操作:

select users.id as 'user id'
, users.full_name as 'user full name'
, users.email as 'user email'
, pc.c as 'count of published'
from users
inner join (select user_id, count(1) as c from products where is_published = 1 group by user_id) as pc on pc.user_id = users.id
Where is_active = 1
order by 'user id' asc;