如何改进此查询？ [英] How to improve this query ?

问题描述

我有2个表客户和客户地址。 CustomerId是客户地址表中的外键。每个客户可以在不同国家/地区拥有多个地址，即美国，加拿大和墨西哥我正在加入这些表格以获得综合结果。现在，如果客户在客户地址表中没有记录，则应显示具有空地址的客户记录。如果客户有多个地址，则美国地址优先，然后是加拿大，然后是墨西哥地址，这意味着它应该只显示每个客户的一条记录，其中地址具有最高优先级。我试图使用左连接和联合编写此查询。类似

I have 2 tables customer and customer address. CustomerId is the foreign key in customer address table. Each customer can have multiple addresses in different countries i.e. US, Canada and Mexico. I am joining these tables to get the combined result. Now if a customer doesn't have a record in the customer address table , it should show the customer records with null address. If customer have multiple addresses then US address take precedence followed by Canada and then mexico address meaning it should show only one record per customer with address with topmost precedence. I am trying to write this query using left join and union. Something like

customer left join customeraddress on (customer.customerid = customeraddress.customerid and customeraddress.countrycode = 'US')

UNION

customer left join customeradress on (customer.customerid = customeraddress.customerid and customeraddress.countrycode = 'CA')
and customer.customerid not in (select customerid's that have a us address)

我想知道是否有更好的方法可以在不使用UNION的情况下执行此操作。此外，我正在重新询问客户ID与我们的地址，这似乎不是有效的方式。这种查询是否有任何特定的名称？分层？

I want to know if there is a better way to do this without using UNION. Also I am re-querying to get the customer ids with us address which doesn't seem the efficient way to do it.Is there any particular name for this kind of queries ? hierarchical ?

推荐答案

您可以通过创建AddressPriority表来执行此操作：

You can do this by creating an AddressPriority table:

Priority Countrycode
1        'US'
2        'CA'
3        'MX'

然后使用查询类似于：

WITH Prio as (
    SELECT  CustomerID,Min(priority)
    FROM    customeraddress ca join AddressPriority ap ON ca.countrycode = p.countrycode
    GROUP BY CustomerID
    )
SELECT  *
FROM    customer c JOIN prio p ON p.CustomerID = c.CustomerID
JOIN    AddressPriority ap ON p.Priority = ap.Priority
JOIN    CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND     ca.CountryCode= ap.CountryCode

改为使用Select Case：

Using a Select Case instead:

WITH Prio as (
    SELECT  CustomerID,Min(priority)
    FROM    (
        Select  CustomerID
               ,Case CountryCode
                    When 'US' Then 1
                    When 'CA' Then 2
                    When 'MX' Then 3
                END as Priority
        FROM    CustomerAddress
        ) as Prio
    GROUP BY CustomerID
    )
,addressprio as (
    select  CustomerID
           ,Case prio
                when 1 then 'US'
                when 2 then 'CA'
                When 3 Then 'MX'
            End as CountryCode
    From    Prio
    )
SELECT  *
FROM    customer c JOIN addressprio ap ON ap.CustomerID = c.CustomerID
JOIN    CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND     ca.CountryCode = ap.CountryCode

我不是sql忍者，但如果我不得不写我的查询我这样做：

I'm no sql ninja, but if I had to write I query I'd do it this way:

WITH tmpview (id, n)
     AS (SELECT a.id,
                Max([priority]) n
         FROM   (SELECT id,
                        CASE
                          WHEN countrycode = 'US' THEN 1
                          WHEN countrycode = 'CA' THEN 2
                          WHEN countrycode = 'MX' THEN 3
                        END [priority]
                 FROM   customer c
                        JOIN customeraddress a
                          ON c.id = a.customerid
                 WHERE  countrycode IN ( 'US', 'CA', 'MX' )) orderedadr
         GROUP  BY a.id)
SELECT *
FROM   address a
       JOIN tmpview t
         ON a.id = t.id

我很确定这个查询仍然可以通过以下方式进一步优化他们是sql ninjas。

I'm pretty sure this query can still be further optimized by them sql ninjas.

这篇关于如何改进此查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！