請教列出所有年齡大於21嵗的中國籍男子之person_id之sql語句。

Question

表Person_Attribute結構如下：

field 1: person_id
field 2: attribute_name (gender,nationality,age etc.)
fiield 3: attribute_type (int,double,string etc.)
field 3 : attribute_value (gender(male,female),nationality (chinese,american etc.)

attribute_value 的數據格式為 String

請教列出所有年齡大於21嵗的中國籍男子之person_id之sql語句。

Answer 1

你这个问题只不过是在https://ask.csdn.net/questions/1097516
的基础上多加了一个条件

所以只需要多加一个exists

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'
)
AND EXISTS
(
   SELECT 1 FROM person_attribute pa 
   WHERE p.id=pa.person_id  AND pa.attribute_name='age' AND CONVERT(pa.attribute_value,SIGNED)>21
)
ORDER BY p.name

以后碰到类似的，要学会举一反三