Mysql查询过滤结果

Question

我有两张桌子

TBL 1: property               TBL 2: property_detail
---------------------         --------------------------------------
Id   Name   status            property_id   param       value
---------------------         --------------------------------------
1    X       1                  1          parking      two-wheeler 
2    Y       1                  1          furnishing   furnished  
3    Z       0                  2          parking      car-parking 
4    A       1                  2          furnishing   semi-furnished
5    B       1                  3          furnishing   furnished 
6    C       0                  4          parking      car-parking 

"property_id“列在"property_detail”中是“属性”中"Id“列的外键。

我需要status=1和(param=“泊车”和value=“停车场”)和(param=“摆设”和value=“配饰”)的搜索结果。

从上面的示例表中，结果将是

Result
-------------
id   name
-------------
2     Y

如何做到这一点？

Answer 1

您可以通过使用连接和details表两次获得所需的结果集。

select p.*
from property p
join property_detail d on (p.Id = d.property_id)
join property_detail d1 on (p.Id = d1.property_id)
where p.status=1 
and d.param='parking' and d.value='car-parking'
and d1.param='furnishing' and d1.value='semi-furnished';

另一种方法还可以使用also子句和sum函数使用下面的查询

select p.*
from property p
join property_detail d on (p.Id = d.property_id)
where p.status=1 
group by p.Id
having sum(param="parking" and value="car-parking") 
and sum(param="furnishing" and value="semi-furnished")

DEMO

Answer 2

 SELECT property.id, property.name 
 FROM property INNER JOIN property_detail 
 ON property.id = property_detail.property_id 
 WHERE 
 (`param`="parking" AND `value`="car-parking") 
 AND 
 (`param`="furnishing" AND `value`="furnished")
 AND status = 1;

你能试试这个吗？但我不确定..。但这会给你一个主意。