這種情型下，SQL語句應該如何寫？

有兩個表如下:

表1：Person

field 1: Id
field 2 : Name

表2：Person_Attribute

field 1: person_id
field 2: attribute_name
field 3 : attribute_value

Person_Attribute 表 attribute_name field 之枚舉值 為：gender,nationality.

請問列出所有 gender ='male' 且 nationality='chinese' 之person之SQL語句。

SELECT p.* FROM person p
WHERE EXISTS
(
   SELECT 1 FROM person_attribute pa 
   WHERE p.id=pa.person_id  AND pa.attribute_name='gender' AND pa.attribute_value='male'
)
AND EXISTS
(
   SELECT 1 FROM person_attribute pa 
   WHERE p.id=pa.person_id  AND pa.attribute_name='nationality' AND pa.attribute_value='chinese'
)
order by p.name

表结构这样设计会导致查询比较麻烦，如果数据量很大，可能会出现性能问题，如果业务已经很明确，还是建议把属性字段直接建在person表上

select * from (select p.id, p.name from person p
left join (select a1.id, a1.attribute_value as gender Person_Attribute a1 where a1.attribute_name = "gender") a2 on p.id = a2.id
left join (select a3.id, a3.attribute_value as nationality Person_Attribute a3 where a3.attribute_name = "nationality") a4 on p.id = a4.id)
where  a2.gender ='male' and a4.nationality='chinese'