代码之家 › 专栏 › 技术社区 › ilitirit

从一个表中有多个列指向另一个表的同一主键的表中检索数据?

sql-server sql

ilitirit · 技术社区 · 17 年前

我不负责这个设计,但我必须从这个模式中提取数据,看起来像这样(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、共享值或具有不同的值。它可能有也可能没有账单和/或送货地址。或递送地址,两者可以相同。

问题似乎有两方面:
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

2 回复 | 直到 17 年前

Quassnoi 17 年前

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 .

您应该将所有连接条件重写为equijoins,以便 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

ilitirit 17 年前

FROM (
SELECT Account.*
FROM   (SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
UNION
SELECT Account.*
FROM   (SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
UNION
SELECT Account.*
FROM   (SELECT Contract.Holding_Id     AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Holding_Id     AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Holding_Id    AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
) Account

它的性能优于使用每行子选择或IN子句。