SELECT * FROM (
SELECT
pp.PersonName,
pp.IDNO,
pp.Sex,
(SELECT CID FROM T_SYS_DATADICT WHERE NAME='xb' AND CODE=pp.Sex) AS sextext,
(CASE WHEN job.job_householderid IS NULL THEN '无务工人员' ELSE '有务工人员' END) AS job_info,
(CASE WHEN zaixiao.zaixiao_householderid IS NULL THEN '无在校生' ELSE '有在校生' END) AS zaixiao_info,
(CASE WHEN jb1.jb1_householderid IS NULL THEN '无常见慢性病人员' ELSE '有常见慢性病人员' END) AS jb1_info,
(CASE WHEN jb2.jb2_householderid IS NULL THEN '无特殊慢性病人员' ELSE '有特殊慢性病人员' END) AS jb2_info,
(CASE WHEN jb3.jb3_householderid IS NULL THEN '无重大疾病人员' ELSE '有重大疾病人员' END) AS jb3_info,
(CASE WHEN canji.canji_householderid IS NULL THEN '无残疾人员' ELSE '有残疾人员' END) AS canji_info,
(CASE WHEN db.db_householderid IS NULL THEN '无低保人员' ELSE '有低保人员' END) AS db_info,
(CASE WHEN wb.wb_householderid IS NULL THEN '无五保人员' ELSE '有五保人员' END) AS wb_info,
yearincome.year_income_perp,
monthincome.month_income_perp,
CASE WHEN (pf.farmland='' OR pf.farmland IS NULL OR pf.HouseCount=0) THEN 0 ELSE
(CONVERT(DECIMAL(18,2),pf.farmland / CONVERT(DECIMAL,pf.HouseCount))) END AS farmland_perp,
CASE WHEN (pf.HouseArea='' OR pf.HouseArea IS NULL OR pf.HouseCount=0) THEN 0 ELSE
(CONVERT(DECIMAL(18,2),pf.HouseArea / CONVERT(DECIMAL,pf.HouseCount))) END AS housearea_perp,
(SELECT (',' + mum.PersonName)
FROM Basic_PoorPeople mum WHERE mum.HouseHolderID=pf.HouseHolderID FOR XML PATH('') ) AS allPersonname,
(SELECT (',' + mum.IDNO)
FROM Basic_PoorPeople mum WHERE mum.HouseHolderID=pf.HouseHolderID FOR XML PATH('') ) AS allIdno,
(SELECT (',' + bhd.Name) FROM Basic_HelpDutyPerson bhd
WHERE bhd.ID IN (SELECT HelpDutyPersonID FROM Basic_PoorFamily_HelpDutyPerson_Map WHERE HouseHolderID=pf.HouseHolderID)
FOR XML PATH('') ) AS helpdutypersonnames
FROM (
SELECT * FROM Basic_PoorFamily WHERE DISABLE=0
) pf LEFT JOIN (
SELECT HouseHolderID,PersonName,Sex,IDNO FROM Basic_PoorPeople WHERE DISABLE=0 AND FamilyRelationID='01'
) pp ON pf.HouseHolderID=pp.HouseHolderID
LEFT JOIN (
SELECT DISTINCT y.HouseHolderID AS job_householderid FROM
(
SELECT PoorPeopleID FROM Basic_PoorPeople_Jobs WHERE
(LeaveYear=0 AND LeaveMonth=0
AND (DATEPART(YEAR,GETDATE())*12+DATEPART(MONTH,GETDATE())) >=(EntryYear*12+EntryMonth)
) OR
(LeaveYear!=0 AND LeaveMonth!=0 AND
(DATEPART(YEAR,GETDATE())*12+DATEPART(MONTH,GETDATE())) >=(EntryYear*12+EntryMonth) AND
(DATEPART(YEAR,GETDATE())*12+DATEPART(MONTH,GETDATE())) <=(LeaveYear*12+LeaveMonth)
)
) x INNER JOIN Basic_PoorPeople y ON x.PoorPeopleID=y.ID
) job ON pf.HouseHolderID=job.job_householderid
LEFT JOIN (
SELECT icm1.HouseHolderID,
CASE WHEN (SELECT pf0.HouseCount FROM Basic_PoorFamily pf0 WHERE pf0.HouseHolderID=icm1.HouseHolderID AND pf0.DISABLE=0)=0 THEN 0
ELSE (
CONVERT(DECIMAL(18,2),
(icm1.GongZi+icm1.ShengChan+icm1.CaiChan+icm1.JiHuaShengYu+icm1.DiBao+icm1.WuBao+icm1.YangLao+icm1.ShengTai+icm1.QiTaZhuanYi-icm1.ZhiChu)
/(SELECT pf1.HouseCount FROM Basic_PoorFamily pf1 WHERE pf1.HouseHolderID=icm1.HouseHolderID AND pf1.DISABLE=0 ))
) END AS year_income_perp
FROM Basic_PoorFamily_Income icm1 WHERE icm1.IncomeYear = CONVERT(VARCHAR(10),DATEPART(YEAR,GETDATE()))
) yearincome ON pf.HouseHolderID=yearincome.HouseHolderID
LEFT JOIN (
SELECT icm2.HouseHolderID,
CASE WHEN (SELECT pf3.HouseCount FROM Basic_PoorFamily pf3 WHERE pf3.HouseHolderID=icm2.HouseHolderID AND pf3.DISABLE=0)=0 THEN 0
ELSE (
CONVERT(DECIMAL(18,2),
(icm2.GongZi+icm2.ShengChan+icm2.CaiChan+icm2.JiHuaShengYu+icm2.DiBao+icm2.WuBao+icm2.YangLao+icm2.ShengTai+icm2.QiTaZhuanYi-icm2.ZhiChu)
/(SELECT pf2.HouseCount FROM Basic_PoorFamily pf2 WHERE pf2.HouseHolderID=icm2.HouseHolderID AND pf2.DISABLE=0 )
/DATEPART(MONTH,GETDATE()) )
) END AS month_income_perp
FROM Basic_PoorFamily_Income icm2 WHERE icm2.IncomeYear = CONVERT(VARCHAR(10),DATEPART(YEAR,GETDATE()))
) monthincome ON pf.HouseHolderID=monthincome.HouseHolderID
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS zaixiao_householderid FROM Basic_PoorPeople WHERE disable=0 AND InSchoolStatusID!='01'
) zaixiao ON pf.HouseHolderID=zaixiao.zaixiao_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS jb1_householderid FROM Basic_PoorPeople WHERE disable=0 AND pp_health='2'
) jb1 ON pf.HouseHolderID=jb1.jb1_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS jb2_householderid FROM Basic_PoorPeople WHERE disable=0 AND pp_health='3'
) jb2 ON pf.HouseHolderID=jb2.jb2_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS jb3_householderid FROM Basic_PoorPeople WHERE disable=0 AND pp_health='4'
) jb3 ON pf.HouseHolderID=jb3.jb3_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS canji_householderid FROM Basic_PoorPeople WHERE disable=0 AND pp_health='5'
) canji ON pf.HouseHolderID=canji.canji_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS db_householderid FROM Basic_PoorPeople WHERE disable=0 AND ifdborwb='1'
) db ON pf.HouseHolderID=db.db_householderid
LEFT JOIN (
SELECT DISTINCT HouseHolderID AS wb_householderid FROM Basic_PoorPeople WHERE disable=0 AND ifdborwb='2'
) wb ON pf.HouseHolderID=wb.wb_householderid
) result
进群与大神交流，【web前端交流学习群018】群号498854752
