sql - 从表中检索数据，其中一个表的多个列指向另一个中的相同主键？

Question

我不负责这个设计，但我必须从这个模式中提取数据，看起来像这样(SQL Server 2000):

CREATE TABLE contract
(
    contract_id int,
    account_id int,             /* account table */
    responsible_id int,         /* account table */
    holding_id int,             /* account table */
    billingaddress_id int,      /* address table */
    deliveryaddress_id int,     /* address table */
)

CREATE TABLE address
(
    address_id int,
    postalcode char(4),
)

CREATE TABLE account
(
    account_id int,
    firstname varchar(40),
    billingaddress_id int,      /* address table */
    deliveryaddress_id int,     /* address table */
)

合约表上的 account_id、responsible_id 和 holding_id 可以为 null、共享值或具有不同的值。它可能有也可能没有账单和/或送货地址。
帐户实体始终具有帐单地址或送货地址，并且两者可以相同。

我必须找到所有与契约(Contract)关联的账户(即契约(Contract)与账户 ID 具有相同的账户、负责或持有 ID)，并且与具有特定邮政编码的地址相关联(直接或通过契约(Contract)) .

问题似乎有两个方面:
a) 检索与契约(Contract)相关的账户
b) 过滤 (a) 中的结果以获取与某个邮政编码相关联的帐户

这是行不通的，因为如果 account_id 与相关邮政编码不相关，但 Holding_id 是，则不会返回:

FROM account
INNER JOIN contract
    ON account.account_id = 
        CASE WHEN NOT IsNull(contract.account_id) THEN contract.account_id 
        WHEN NOT IsNull(contract.responsible_id) THEN contract.responsible_id 
        ELSE contract.holding_id END

由于某种原因，这太慢了(FK 没有编入索引 - 等了 30 分钟，但没有返回):

FROM account
INNER JOIN contract
    ON account.account_id = contract.account_id
    OR account.account_id = contract.responsible_id
    OR account.account_id = contract.holding_id

似乎唯一有效的是 UNION，但是我仍然面临按地址类型过滤结果的问题。

返回所需结果的最短方法是什么？目前我倾向于创建一个临时表来存储中间数据。

Answer 1

SELECT  *
FROM    contract
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    account
        JOIN    address
        ON      address_id IN (billingaddress_id, deliveryaddress_id)
        WHERE   account_id IN (account_id, responsible_id, holding_id)
                AND postalcode = @mycode
        )

要选择帐户，请使用:

SELECT  *
FROM    account ao
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    (
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.account_id = ao.account_id
                UNION ALL
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.responsible_id = ao.account_id
                UNION ALL
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.holding_id = ao.account_id
                ) co
        JOIN    account ai
        ON      ai.account_id IN (co.account_id, co.responsible_id, co.holding_id)
        JOIN    address
        ON      address_id IN (billingaddress_id, deliveryaddress_id)
        WHERE   postalcode = @mycode
        )

更新:

由于您的列未编入索引， EXISTS在这种情况下效率不高，因为它不能重写为 IN .

您应该将所有连接条件重写为等连接，以便 HASH JOIN方法可用。

尝试这个:

SELECT  a.account_id
FROM    (
        SELECT  account_id
        FROM    contract
        UNION
        SELECT  responsible_id
        FROM    contract
        UNION
        SELECT  holding_id
        FROM    contract
        ) c
JOIN    (
        SELECT  account_id, billingaddress_id AS address_id
        FROM    account
        UNION
        SELECT  account_id, deliveryaddress_id
        FROM    account
        ) a
ON      a.account_id = c.account_id
JOIN    address ad
ON      ad.address_id = a.address_id
WHERE   ad.postalcode = @mycode