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

Question

有兩個表如下:

表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語句。

Answer 1

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表上