MySQL 查询与选择和分组

Question

我想选择 10 提交的所有客户记录，但不选择 20 被指定为代表的客户记录。所以我想要的结果是为客户端 2 和 3 选择 2 条记录。4 被排除，因为 20 是该组的一部分。非常感谢任何帮助。

+--------------+--------+----------------+
| Submitted_By | Client | Representative |
+--------------+--------+----------------+
|           10 |      2 |             15 |
|           10 |      2 |             16 |
|           10 |      2 |             17 |
|           10 |      3 |             15 |
|           10 |      3 |             16 |
|           10 |      3 |             19 |
|           10 |      4 |             15 |
|           10 |      4 |             16 |
|           10 |      4 |             17 |
|           10 |      4 |             20 |
+--------------+--------+----------------+

已尝试

SELECT
    Client,
    Submitted_By,
    Representative,
FROM
    client
WHERE
Client = 10 AND Representative != 20

Answer 1

您可以使用子查询，例如:

SELECT Client, Submitted_By, Representative 
   FROM client 
   WHERE Submitted_By = 10 
   AND Client NOT IN (SELECT Client FROM client WHERE Representative = 20)

您写道您只想返回“总共 2 条记录”。然后是GROUP BY Client语句可用于从您提供的示例表中返回两行，每个客户一行。