mysql - 如何根据列值过滤结果

Question

我的查询中有这个结果集。

+-------------+-------------+-----------+----------+------------------+---------------+----------------+
| Branch Name |  Client ID  |   Name    |  Loans   | Savings Deposits | Time Deposits | Share Capital  |
+-------------+-------------+-----------+----------+------------------+---------------+----------------+
| Main Office | 01-029203-1 | Doe, John |     0.00 |             0.00 |          0.00 |           0.00 |
| Main Office | 01-012314-1 | Doe, John | 51200.75 |            61.26 |      15000.00 |           0.00 |
| Main Office | 01-929828-3 | Doe, Jane |  3000.00 |             0.00 |       8000.00 |           0.00 |
| Main Office | 01-992830-4 | Doe, Jane |  5240.44 |            46.00 |       2000.00 |           0.00 |
+-------------+-------------+-----------+----------+------------------+---------------+----------------+

我想过滤掉某个客户名称的客户 ID 之一(例如:Doe、John，客户 ID 为 01-029203-1)的所有 4 个账户(贷款、储蓄存款、时间)的余额为 0存款和股本)，该客户的所有实例将不包括在内。

就像这样:

+-------------+-------------+-----------+----------+------------------+---------------+----------------+
| Branch Name |  Client ID  |   Name    |  Loans   | Savings Deposits | Time Deposits | Share Capital  |
+-------------+-------------+-----------+----------+------------------+---------------+----------------+
| Main Office | 01-929828-3 | Doe, Jane |  3000.00 |             0.00 |       8000.00 |           0.00 |
| Main Office | 01-992830-4 | Doe, Jane |  5240.44 |            46.00 |       2000.00 |           0.00 |
+-------------+-------------+-----------+----------+------------------+---------------+----------------+

我已经尝试过使用 where 和 having 子句，但我不知道如何使用。

Answer 1

一个选项，使用四列的总和:

SELECT *
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable WHERE t1.Name = t2.Name AND
                  Loans + `Savings Deposits` + `Time Deposits` + `Share Capital` = 0);

这个答案的工作原理是，针对每条记录搜索是否可以找到任何具有相同名称且所有帐户总和为零的记录。如果是，则该名称无效，所有此类名称记录都不会出现在结果集中。