难题---SQL

Question

表(SD_subject)
subject_id       subject_name                
2            户口类别             
3            经济状况            
4            住所类型                                    
8            健康状况 .........................表(SD_subject_list)
subject_id  list_id  list_name
2           0        未落常住户口
2           1       非农业家庭户口
2           2       农业家庭户口
3           1       富裕
3           2       小康
4           0       未知
4           1       自有
4           2       公房........................................表(BD_farmily)
RPR_id    RPR_type  HH_name  economy_status  house_type                                                                                                                                         
142001    2         小明     1               0
155444    1         小化     2               3这个报表的sql怎么写RPR_id  RPR             name   economy         house
142001  农业家庭户口    小明    富裕           未知
155444  非农业家庭户口  小化    小康           公房

Answer 1

select RPR_id ,b.list_name,HH_name,c.subject_name from db_farmily
left join SD_subject b on RPR = b.subject_id
left join SD_subject_list c on economy_status = c.list_id大概是上面这样，我没数据测试，你试试看

Answer 2

select RPR_id ,b.list_name,HH_name,c.subject_name from db_farmily a,SD_subject b,SD_subject c where a.RPR=b.subject_id and a.economy_status=c.list_id

Answer 3

select RPR_id,(select list_name from SD_subject_list where subject_id=2 and list_id=BD_farmily.list_id) RPR,HH_name,(select list_name from SD_subject_list where subject_id=3 and list_id=BD_farmily.economy_status) economy,(select list_name from SD_subject_list where subject_id=4 and list_id=BD_farmily.house_type) house from BD_farmily

Answer 4

非常感谢楼上几个朋友!特别是pengdali(大力 V2.0)!看了你的代码,我明白了.完整代码如下:
select RPR_id,
(select SD_subject_list.list_name from SD_subject_list
 join SD_subject on SD_subject.subject_id=SD_subject_list.subject_id 
 where SD_subject.subject_name='户口类别' and SD_subject_list.list_id=BD_farmily.rpr_type
) As RPR,
HH_name,
(select SD_subject_list.list_name from SD_subject_list 
 join SD_subject on SD_subject_list.subject_id=SD_subject.subject_id
 where SD_subject.subject_name='经济状况' and SD_subject_list.list_id=BD_farmily.economy_status
) As economy,
(select SD_subject_list.list_name from SD_subject_list
 join SD_subject on SD_subject_list.subject_id=SD_subject.subject_id 
 where SD_subject.subject_name='住所类型' and SD_subject_list.list_id=BD_farmily.house_type
) As house 
from BD_farmily

Answer 5

to:pengdali(大力 V2.0) 
我经过测试,怎样速度太慢了!有什么办法可以再改进的吗?