mysql - MySQL:如何显示不包含特定值的行/单元格？

例如我有

+------------+--------+-----------+
| PRIMARYKEY | NAME   | TYPE      |
+------------+--------+-----------+
| 0001       | Adam   | Import    |
| 0001       | Adam   | Transfer  |
| 0001       | Adam   | Payment   |
| 0002       | Brooke | Import    |
| 0002       | Brooke | Transfer  |
| 0003       | Chloe  | Import    |
| 0003       | Chloe  | Transfer  |
| 0003       | Chloe  | Payment   |
| 0004       | David  | Import    |
| 0004       | David  | Transfer  |
| 0005       | Emily  | Import    |
| 0005       | Emily  | Transfer  |
+------------+--------+-----------+

这是我的问题

SELECT
  account.PRIMARYKEY,
  client.NAME,
  transaction.TYPE,
FROM
  account
    JOIN
  client ON account.ACCOUNTKEY = client.PRIMARYKEY
    JOIN
  transaction ON account.PRIMARYKEY = transaction.ACCOUNTKEY

我想得到所有没有Payment的数据，然后按PRIMARYKEY分组

+------------+--------+-----------+
| PRIMARYKEY | NAME   | TYPE      |
+------------+--------+-----------+
| 0002       | Brooke | Import    |
| 0004       | David  | Import    |
| 0005       | Emily  | Import    |
+------------+--------+-----------+

我试过了

WHERE
  transaction.TYPE NOT LIKE 'Payment'

但是失败了
谢谢你的帮助。

最佳答案

您可以使用NOT EXISTS。WHERE子句中的内部查询将获取所有已付款的帐户，但由于使用了NOT EXISTS，它将排除内部查询中的所有帐户。

SELECT  account.PRIMARYKEY,
        client.NAME,
        MIN(t.TYPE)
FROM    account
        JOIN client ON account.ACCOUNTKEY = client.PRIMARYKEY
        JOIN transaction t ON account.PRIMARYKEY = t.ACCOUNTKEY
WHERE   NOT EXISTS (
                        SELECT  1
                        FROM    transaction a
                        WHERE   a.ACCOUNTKEY = t.ACCOUNTKEY
                                AND a.Type = 'Payment'
                    )
GROUP   account.PRIMARYKEY,
        client.NAME

这里有一个指南显示了performance between IN, JOIN, EXISTS中的差异。

关于mysql - MySQL:如何显示不包含特定值的行/单元格？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48878668/