我有以下表格:
tb_item
id_item | item_name | price
----------------------------
1 | item1 | 2000
2 | item2 | 3000
3 | item3 | 4000
和
tb_value
id_value | id_item | value_type | value
----------------------------------------
1 | 1 | bedroom | 2
2 | 1 | bathroom | 1
3 | 2 | bedroom | 3
4 | 2 | bathroom | 1
5 | 3 | bedroom | 4
6 | 3 | bathroom | 2
我希望得到这样的输出:
item_name | price | bedroom | bathroom
---------------------------------------
item1 | 2000 | 2 | 1
item2 | 3000 | 3 | 1
item3 | 4000 | 4 | 2
我正在使用PostgreSQL;可以使用什么查询来获得此输出?我也在用PHP;有没有一个PHP函数可以做到这一点?
您还可以使用几个LEFT JOIN
语句
SELECT i.item_name
, i.price
, bed.value AS bedroom
, bath.value AS bathroom
FROM tb_item AS i
LEFT JOIN tb_value AS bed ON i.id_item = bed.id_item
AND bed.value_type = 'bedroom'
LEFT JOIN tb_value AS bath ON i.id_item = bath.id_item
AND bath.value_type = 'bathroom'
select item_name, price, bedroom.value as bedroom, bathroom.value as bathroom
from tb_item, tb_value as bedroom, tb_value as bathroom
where bedroom.id_item=tb_item.id_item and bedroom.value_type='bedroom'
and bathroom.id_item=tb_item.id_item and bathroom.value_type='bathroom'