MYSQL 使用 IF 选择计数

Question

我想让所有拥有多个具有部门状态的订单的用户

我的查询:

SELECT 
    users.id as "Id", 
    users.firstname as "Firstname", 
    users.lastname as "Lastname",
    COUNT(IF(orders.status = 0, 1, 0)) as "Status #0",
    COUNT(IF(orders.status = 1, 1, 0)) as "Status #1",
    COUNT(IF(orders.status = 2, 1, 0)) as "Status #2",
    COUNT(IF(orders.status = 3, 1, 0)) as "Status #3",
    COUNT(IF(orders.status = 4, 1, 0)) as "Status #4",
    COUNT(IF(orders.status = 5, 1, 0)) as "Status #5",
    COUNT(IF(orders.status = 6, 1, 0)) as "Status #6",
    COUNT(IF(orders.status = 7, 1, 0)) as "Status #7",
    COUNT(IF(orders.status = 8, 1, 0)) as "Status #8"
FROM 
    users 
    LEFT JOIN orders ON orders.idu = users.id 
WHERE 
    users.register_complete = 1 
GROUP BY 
    users.id 
ORDER BY 
    users.date_register DESC

结果:

所有状态都相同......问题出在哪里？谢谢! :)

Answer 1

如果 value 不是 null，

count(value) 将计为 1，请参见 documentation :

COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

因此，您计算了每一行，与 IF 条件无关。

将 count 更改为 sum，或将 0 更改为 null 以获得所需的结果:

...
COUNT(IF(orders.status = 0, 1, null))
...