如何改进此查询？

编程入门行业动态更新时间:2024-10-15 04:18:35

本文介绍了如何改进此查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有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