因此,您必须首先在RELATIONS中从管理器中查找所有clientid,但要删除所有已分配了管理器的客户机。
根据表的大小,您可能要重写它,但这是一种方法:
1)获取所有没有管理员的客户端ID:
SELECT R1.client_id,SUM(R1.assigned) as sumassigned FROM relationships AS R1 GROUP BY R1.client_id HAVING ( SUM(R1.assigned) = 0)
现在更容易了,您只需加入即可,例如:
SELECT R2.client_id,R2.manager_id FROM relationships AS R2
INNER JOIN
(SELECT R1.client_id,SUM(R1.assigned) as sumassigned FROM relationships AS R1 GROUP BY R1.client_id HAVING ( SUM(R1.assigned) = 0) ) AS DRVNOMANAGER
ON (R2.client_id = DRVNOMANAGER.client_id)
WHERE (R2.manager_id = 123)
未经测试。(这意味着您可能需要对其进行修复)
这个想法是创建一个包含所有没有管理者的客户的派生(临时)表,然后对原始问题(“这个管理者123知道哪些客户没有分配其他客户”)进行内部联接
能解决您的问题吗?
PS:用PHP更容易解决这些问题,但是如果您的数据集很大,那是不可行的。
OP询问客户名称,所以只需添加:
SELECT R2.client_id,R2.manager_id,C.client_name FROM relationships AS R2
INNER JOIN
(SELECT R1.client_id,SUM(R1.assigned) as sumassigned FROM relationships
AS R1 GROUP BY R1.client_id HAVING ( SUM(R1.assigned) = 0) ) AS DRVNOMANAGER
ON (R2.client_id = DRVNOMANAGER.client_id)
INNER JOIN clients AS C ON (C.client_id = R2.client_id)
WHERE (R2.manager_id = 123)
最后一个请求:删除了已删除的关系:
只需将WHERE子句添加到内部DRVNOMANAGER中,就可以限制GROUP BY中使用的行。例如:
SELECT R2.client_id,SUM(R1.assigned) as sumassigned FROM relationships
AS R1 WHERE (NOT(R1.deleted = 1) ) GROUP BY R1.client_id HAVING ( SUM(R1.assigned) = 0) ) AS DRVNOMANAGER
ON (R2.client_id = DRVNOMANAGER.client_id)
INNER JOIN clients AS C ON (C.client_id = R2.client_id)
WHERE (R2.manager_id = 123)
=============================================
这是我的老答案。不再相关。
“选择我的所有未分配任何潜在客户的客户。”
如果我读得很好,则意味着:从RELATIONS获得所有clientid,其中给出了一些managerid,并且分配的为0。 (assigned = 0表示“没有人被指定为牵头人。”)
对吗?
然后,您最终得到这样的内容(对于managerid 123):
SELECT R.clientid,C.clientname FROM RELATIONSHIPS AS R WHERE ( (R.managerid = 123) AND (R.assigned=0))
INNER JOIN CLIENTS AS C ON (C.clientid = R.clientid)
我删除了列名中的空格,因为我讨厌列名中的空格。
,
SELECT c.*
FROM managers m
JOIN relationships r
ON r.manager_id = m.manager_id
JOIN clients c
ON c.client_id = r.client_id
LEFT
JOIN relationships x
ON x.client_id = c.client_id
AND x.assigned = 1
WHERE m.manager_id = 123
AND r.assigned = 0
AND x.client_id IS NULL;
+-----------+-------------+
| client_id | client_name |
+-----------+-------------+
| 789 | Tyler |
+-----------+-------------+
本文链接:https://www.f2er.com/3166455.html