如何在SQL中检查一对多关系

Question

来自银行领域，我有一个客户和与之关联的许多账户。

Client 1 - Account A
Client 1 - Account B
Client 2 - Account A
Client 2 - Account B
Client 2 - Account C

在账户表中，有一个状态标志，我想检查是否为1个客户的所有账户设置为“Y”。 当一个客户的所有账户都将该标志设置为“Y”时，我们通过测试。 有人知道如何在SQL中检查吗？ 我已经尝试了以下脚本的分组方式，但似乎不起作用：

select client_number
from client_table A, account table B
where B.flag = 'Y'
group by client number having count(*) =1

- user3016095

---

请删除您的count(*) = 1，因为在您的情况下，一个客户拥有多个帐户。 - Suleman Ahmad

我不确定它对你实际上是如何工作的。我没有看到你是如何连接你的client_table和account_table的。 - cha

请提供您的数据库描述。 - Veer Shrivastav

@cha，楼主说它不起作用;-) - WarrenT

我希望我的评论被视为友好的鼓励，继续努力。一个好的脑筋急转弯通常是学习的好方法。 - WarrenT

Answer 1

使用count()>1并在这两个表之间建立关系。我假设您已经使用客户编号在这两个表之间建立了关系。

select A.client_number
from client_table A, account table B
where  A.client_number=B.client_number
group by B.client_number having count(*) >1

- DevT

---

这会忽略任何状态不为“Y”的行，那么你怎么知道它们中是否有任何一个是“N”？ - WarrenT

@WarrenT编辑了我的答案，删除了B.flag = 'Y'。这样就可以获取所有可用的记录。 - DevT

但是你怎么知道它们是否全部都是“Y”或者有任何一个不是“Y”呢？ - WarrenT

Answer 2

试试这个，

select client_number
from client_table A, account table B
where B.flag = 'Y' AND B.flag != 'N'
group by client number having count(*) > 0

- Suleman Ahmad

---

这会忽略任何状态不为“Y”的行，那么你怎么知道它们中是否有任何一个是“N”？ - WarrenT

@WarrenT：谢谢！那么计数条件将变为大于0而不是大于1。因为如果只有任何客户的一个帐户，那就不应该跳过。 - Suleman Ahmad

你需要知道至少有一个是‘Y’，零个不是‘Y’。 - WarrenT

Answer 3

试一下这个

select A.client_number 
from client_table A join account_table B on A.client_number=B.Client_number
where B.flag = 'Y'
group by A.client_number 
having count(*) >1

- vhadalgi

---

这会忽略任何状态不为“Y”的行，那么你怎么知道它们中是否有任何一个是“N”？ - WarrenT

Answer 4

假设在account_table表中有id列，假设在client_table表中有id_account列作为对account_table表中id列的外键。以下是我认为符合您需求的 ANSI 1992 版本：

SELECT 
    a.client_number, 
    COUNT(DISTINCT(a.id_account)) AS distinct_accounts,
    COUNT(DISTINCT(IF(ISNULL(b.id), 0, 1))) AS distinct_flagged_accounts
FROM 
    client_table a
        LEFT JOIN account_table b ON a.id_account = b.id AND b.flag = 'Y'
GROUP BY a.client_number 
HAVING COUNT(DISTINCT(a.id_account)) = COUNT(DISTINCT(IF(ISNULL(b.id), 0, 1)))
;

FIDDLE: http://sqlfiddle.com/#!2/aed84/1

代码演示：http://sqlfiddle.com/#!2/aed84/1

- Sebas

---

客户表中有1条记录，其中5个帐户标记为“Y”，2个帐户标记为“N”，那么您的连接将返回5个“Y”帐户的行。因此，您不会计算任何非“Y”标记的帐户。 - WarrenT

@WarrenT，不好意思，这是左连接。请查看 SQL Fiddle。 - Sebas

好的。我没有想到你的数据模型会有每个客户多行客户数据，并且每个数据都有指向不同账户的键。人们可能认为银行的“客户”表格每个客户只有一行，包含关于该客户的信息，例如姓名、联系方式、税号等等。由于一个客户可以拥有多个账户，所以通常会在账户中放置客户键，而不是相反。因此，这是一个有效的解决方案，但是除非您在问题中理解了一些我错过的内容，否则它很可能不适用于用户的数据库。 - WarrenT

@WarrenT 嗯，根据他的第一张表格（“客户1-帐户A”）提取的信息，我认为他采用了这种设计，显然是错误的设计，但那是他的选择。现在我想这可能不是实际情况。我们需要进一步了解他的实现细节。 - Sebas

啊，是的，好观点。我现在明白了它可能被解释成那样。我只是认为这是列出逻辑关系的简单方法。正如你所说，我们确实需要更多细节。 - WarrenT

Answer 5

我假设你的账户表包含客户号，因此这将用于连接表。但如果你只想要符合此条件的客户号，那么听起来你只需要检查一个表，除非你需要结果中未提及的其他字段。

HAVING子句将在分组操作后检查结果。我们希望有一个组，其中客户的所有账户具有相同的值，如count(distinct status)=1所示，并且客户的最高状态值（在本例中为所有状态值）为“Y”。

select client_number
  from account_table
  group by client_number
  having count(distinct status) = 1
     and max(status) = 'Y'

然而，如果它们由一个ID字段连接在一起，那么也许您需要同时使用这两个表才能从客户表中获取客户编号。

select client_number
  from client_table
  where client_id in (select client_id
                        from account_table
                        group by client_id
                        having count(distinct status) = 1
                           and max(status) = 'Y'
                     )

- WarrenT

Answer 6

为了获取您想要的数据，您需要编写两个SELECT语句。我假设了您的表结构可能是什么样子，因此您可能需要调整我使用的某些列名。 第一个SELECT将为您提供实际要比较的信息。它会给出所有客户（按其编号），他们拥有的账户总数，以及CASE语句帮助您确定状态为“Y”的账户数量。

SELECT client_number, COUNT(*) NumOfAccts, SUM(
    CASE WHEN A.Status = 'Y' THEN 1 ELSE 0 END) NumOfY
FROM Accounts A
GROUP BY client_number;

这可能足以确定谁通过谁失败。但如果您需要客户名称和实际的通过/失败文本，我们将使用此SELECT语句作为子查询，然后将其连接回您的客户表。

SELECT client_name, CASE WHEN NumOfAccts = NumOfY THEN 'Pass' ELSE 'Fail' END PassTest
FROM Clients c INNER JOIN
(
SELECT client_number, COUNT(*) NumOfAccts, SUM(
    CASE WHEN A.Status = 'Y' THEN 1 ELSE 0 END) NumOfY
FROM Accounts A
GROUP BY client_number
) s ON c.client_number = s.client_number;

正如您所看到的，我们重新加入客户表只是为了获取他们的姓名。然后我们有另一个CASE语句来比较带有“是”状态的账户数与所有账户数的数量。如果它们相等，我们将输出“通过”。否则，我们输出“失败”。

就是这样。

- Ellesedil