我有这些下表：
suppliers
id | name
-----------
1 | sample
addresses
id | address | owner_type | owner_id
-------------------------------------
1 | adres | Supplier | 1
phone_numbers
id | number | owner_type | owner_id | phone_type
------------------------------------------------
1 | 12345 | Supplier | 1 | phone
2 | 67890 | Supplier | 1 | fax
地址表对我来说似乎不是问题，我使用此查询，对其进行了测试，并且工作正常。
Select suppliers.*, addresses.address AS address FROM suppliers LEFT JOIN addresses ON (addresses.owner_id = suppliers.id AND addresses.owner_type = 'Supplier')
现在问题出在phone_number表中，有2条记录和2个不同的条件，如您所见：
id | number | owner_type | owner_id | phone_type
------------------------------------------------
1 | 12345 | Supplier | 1 | phone
2 | 67890 | Supplier | 1 | fax
有phone_type->phone和字段fax，我想显示两个结果，像这样：
id | name | address | phone | fax
---------------------------------
1 | sample | adres | 12345 | 67890
我尝试以下查询：
LEFT JOIN phone_numbers ON (phone_numbers.owner_id = suppliers.id AND phone_numbers.owner_type = 'Supplier' AND phone_type = 'phone')
不幸的是，该查询仅查询1个条件（phone），我如何获得传真值？
我使用PostgreSQL。
您可以使用不同的别名两次连接phone_numbers表，一次用于电话，一次用于传真。如下面的P1（用于电话）和P2（用于传真）。
Select suppliers.*, addresses.address AS address ,P1.number as 'Phone', P2.number as 'Fax'
FROM suppliers
LEFT JOIN addresses ON (addresses.owner_id = suppliers.id AND addresses.owner_type = 'Supplier')
LEFT JOIN phone_numbers P1 ON (P1.owner_id = suppliers.id AND P1.owner_type = 'Supplier' AND P1.phone_type = 'phone')
LEFT JOIN phone_numbers P2 ON (P2.owner_id = suppliers.id AND P2.owner_type = 'Supplier' AND P2.phone_type = 'fax')
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句