我会尽量直截了当地谈到我在这里要实现的目标。。。
我有一个有效的查询,但我最近发现了一个问题。现在,我可以用PHP简单地解决这个问题,但我知道它可以在MySQL中以某种方式完成。。。我只是不知道怎么做。这就是正在发生的事情:
我有一个USERS表和一个SERVICES表。一个用户可以有多个SERVICES(每个服务彼此完全独立),每个服务都有一个名为"status"的字段。状态定义服务的当前状态:活动/非活动
当您转到"管理用户"页面时,会调用该查询,在该页面中,每个用户都被划分到其所属的类别中。例如,它将是:活动用户|停用用户|其他用户(根本没有任何服务计划的用户)。
因此,我的停用用户查询归结为:
SELECT DISTINCT u.* FROM users u LEFT JOIN services s ON s.assignedto=u.id WHERE s.status!=1
该语句非常有效,但是,如果客户有两个服务计划,其中一个被激活,另一个被停用,那么他将出现在停用列表和激活列表中。该语句需要一个条件,如果用户也有激活的服务计划,则该语句将从查询中排除该用户。现在,我有一些用户属于不应该存在的停用用户。
无论如何,提前谢谢你!
您可以按照@GarethD的建议用not exits()
重写查询。
如果规模不好,另一种需要考虑的方法可能是按条款分组,例如:
SELECT u.*
FROM users u
LEFT JOIN services s ON s.assignedto=u.id
GROUP BY u.id
HAVING max(s.status) <> 1
不过,最好的选择是增加一个字段。如果将status
字段添加到users
,则可以直接对其进行索引和查询。你可以使用触发器来维护它,但就个人而言,这是一种罕见的情况,我发现在应用程序中维护它是一种更好的方法。(基本上,您永远不知道何时需要在不干扰其他DB表的情况下快速将用户标记为非活动用户。)
您可以使用NOT EXISTS
:排除具有活动服务的用户
SELECT u.*
FROM users u
WHERE NOT EXISTS
( SELECT 1
FROM Services s
WHERE s.assignedto=u.id
AND s.status = 1
);
为了得到你想要的计数,我想你需要这样的东西:
SELECT COUNT(CASE WHEN s.ActiveServices > 0 THEN 1 END) AS ActiveUsers,
COUNT(CASE WHEN s.ActiveServices = 0 THEN 1 END) AS DeactivatedUsers,
COUNT(CASE WHEN s.assignedto IS NULL THEN 1 END) AS OtherUsers
FROM users u
LEFT JOIN
( SELECT s.assignedto,
COUNT(CASE WHEN s.status = 1 THEN 1 END) AS ActiveServices
FROM Services s
GROUP BY s.assignedto
) s
ON s.assignedto = u.ID
我刚刚记得NOT EXISTS在MySQL中不如LEFT JOIN/is NULL高效,Denis也指出NOT EXISTS的伸缩性不好。LEFT JOIN方法是:
SELECT u.*
FROM Users u
LEFT JOIN Services s
ON s.assignedto = u.id
AND s.status = 1
WHERE s.assignedto IS NULL;