我有一个名为accounts的MySQL表.在此表中有一个名为salesmanager_id的字段. 99％的时间,此字段中的值对于特定客户的所有帐户(使用customer_id指定)始终相同.
我要做的是找到分配给多个销售经理的客户的customer_id.例如：
+------------------------------------+
| id | customer_id | salesmanager_id |
|------------------------------------|
| 1 | 12 | 4 |
| 2 | 12 | 4 |
| 3 | 14 | 3 | <-- I want this customer_id
| 4 | 14 | 4 |
+------------------------------------+
在上面的示例中,customer_id 14同时分配了salesmanager_id 3和4.我想为我的列表检索该customer_id.
我尝试了以下查询,但返回一个空结果(虽然我确定至少存在一些差异).
SELECT `name`,`customer_id` AS `customer`,`salesmanager_id` FROM `accounts`
WHERE `salesmanager_id` NOT IN (
SELECT `salesmanager_id` FROM `accounts` a
LEFT JOIN `customers` c ON (a.customer_id = c.id)
WHERE a.customer_id=c.id
) GROUP BY `customer`;
最佳答案
SELECT
customer_id
FROM
accounts
GROUP BY
customer_id
HAVING
COUNT(DISTINCT salesmanager_id) > 1
这基本上获取每个customer_id的所有salesmanager_id,如果有多个唯一的salesmanager_id值,则返回customer_id.