使用 mysql 检索地址的最佳方法

Question

考虑我有两个名为地址和客户的表，显示在下面的代码中。

客户表：

customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id

地址表：

address table with fields as address_id, customer_id, line1, line2, city, state, zip_code, phone, disabled

我需要获得的是基于客户 ID 的账单和送货地址。

像这样：

first_name | last_name | billing_line_1 | billing_line_2 | billing_state | billing_zip_code | billing_phone | shipping_line_1 | shipping_line_2 | shipping_city | shipping_city | shipping_state | shipping_zip_code | shipping_phone 

我正在使用这样的东西：

SELECT customers.first_name, customers.last_name, addresses.line1 ,addresses.line2 
FROM `addresses`INNER JOIN customers 
on addresses.address_id = customers.customer_id

我的问题是：

如何将同一字段标记为 billing_line_1 和 shipping_line_1 的两倍？

更新：

基于连接，如果我在选择语句中重复字段，它会返回两个地址相同，而送货地址和账单地址都根据address_id单独存储。

Answer 1

使用相同的

字段，例如，

SELECT customers.first_name, customers.last_name,billing.line1 as 
billing_line_1,billing.line2 as 
billing_line_2,shipping.line1 as shipping_line_1,shipping.line2 as shipping_line_2  FROM customers 
JOIN addresses AS shipping ON customers.shipping_address_id = shipping.address_id 
JOIN addresses AS billing ON 
customers.billing_address_id = billing.address_id

查询的工作演示可在

SQL FIDDLE

http://sqlfiddle.com/#!9/61db31/1/0

Answer 2

您可以使用别名来实现此目的，例如：

SELECT customers.first_name, customers.last_name, addresses.line1 AS billing_line_1,addresses.line2 AS shipping_line_1
FROM `addresses` INNER JOIN customers
ON addresses.address_id = customers.customer_id

在这里，我将获得在"AS"之后定义的别名的列名。因此，在上面的示例中，输出中的 addresses.line1 列名将billing_line_1，同样，对于 addresses.line2 将作为shipping_line_1。

Answer 3

给定

drop table if exists CustomerAddress;
create table CustomerAddress(id int auto_increment primary key,address_id int, customer_id int, line1 varchar(10),line2 varchar(10));
truncate table CustomerAddress;
insert into CustomerAddress (address_id, customer_id, line1,line2) values
(1,1,'b1_1','b1_2'),(2,1,'s1_1','s1_2'),
(1,2,'b2_1','b2_2'),
(2,3,'s3_1','s3_2');

您可以使用条件聚合（也称为透视）

SELECT c.customerid, 
         max(case when address_id = 1 then line1 else '' end ) as BillingLine1,
         max(case when address_id = 1 then line2 else '' end ) as BillingLine2,
         max(case when address_id = 2 then line1 else '' end ) as ShippingLine1,
         max(case when address_id = 2 then line2 else '' end ) as ShippingLine2
from customers  c
INNER JOIN Customeraddress a
on a.customer_id = c.Customerid
group by c.customerid;

或多个联接地址

SELECT c.customerid,
         a1.line1 as billingline1,a1.line2 as billingline2,
         a2.line1 as shippingline1,a2.line2 as shippingline2
from customers c
left join CustomerAddress a1 on c.customerid = a1.customer_id and a1.address_id = 1
left join CustomerAddress a2 on c.customerid = a2.customer_id and a2.address_id = 2
where a1.address_id is not null or a2.address_id is not null
order by c.customerid
;

至于哪个运行最好，请解释和检查结果。顺便说一句，您应该从客户而不是地址开车。