一段四表联查外加字符拼接的sql，留存备查

 

select DISTINCT [P_ID],[P_CODE],[P_CODE_OLD],[P_NAME],[NATIVE_PLACE],[GENDER],[EDUCATION],[EMPLOY_DATE],[CITY],[IDENTITY_NUM],[OFFICE_NUM],[EMPLOY_TYPE],[P_TYPE],[PHONENO],[HOME_NUM],[DUTY],[BIRTHDAY],[EMAIL],[HOME_ADDR],[OFFICE_ADDR],[CREATER],[CREATE_TIME],[PHOTO],[UPDATER],[UPDATE_TIME],[REMARK],[FLAG],[CARD_NO],[ORG_ID],[ORG_NAME],[FACEFLAG],[FINGERFLAG],[CARDFLAG],[HIK_P_ID],[VEHICLE_PLATE] 
from 
    (  
        SELECT  [P_ID],[P_CODE],[P_CODE_OLD],[P_NAME],[NATIVE_PLACE],[GENDER],[EDUCATION],[EMPLOY_DATE],[CITY],[IDENTITY_NUM],[OFFICE_NUM],[EMPLOY_TYPE],[P_TYPE],[PHONENO],[HOME_NUM],[DUTY],[BIRTHDAY],[EMAIL],[HOME_ADDR],[OFFICE_ADDR],[CREATER],[CREATE_TIME],[PHOTO],[UPDATER],[UPDATE_TIME],[REMARK],[FLAG],[CARD_NO],[ORG_ID],[ORG_NAME],[FACEFLAG],[FINGERFLAG],[CARDFLAG],[HIK_P_ID], 
        VEHICLE_PLATE=stuff
                           ( (SELECT ';'+VEHICLE_PLATE  FROM 
                                                           ( select t4.VEHICLE_PLATE, t2.* from 
                                                                                              (select pv.VEHICLE_ID, t1.* from 
                                                                                                                             ( select o.ORG_NAME,p.* from  T_DATA_PERSON p ,T_SYS_ORGANIZATION o 
                                                                                                                               where 1=1 
                                                                                                                               and p.ORG_ID = o.ORG_ID  
                                                                                                                               and (p.FLAG is null or p.FLAG <> '1') 
                                                                                                                               and p.UPDATE_TIME >= '2017/9/26 17:00:32' 
                                                                                                                               and p.UPDATE_TIME <= '2017/10/26 17:00:32') t1
                                                                                               left join T_DATA_PERSON_VEHICLE pv on t1.P_ID = pv.P_ID
                                                                                               ) t2 
                                                              left join (select * from T_DATA_VEHICLE v  where 1=1) t4 on t2.VEHICLE_ID = t4.VEHICLE_ID
                                                            ) tt  
                              WHERE tt.P_ID=t.P_ID  FOR xml path('')), 1, 1, '') 
         FROM ( select t4.VEHICLE_PLATE, t2.* from 
                                                ( select pv.VEHICLE_ID, t1.* from 
                                                                                (select o.ORG_NAME,p.* from  T_DATA_PERSON p ,T_SYS_ORGANIZATION o 
                                                                                 where 1=1 
                                                                                 and p.ORG_ID = o.ORG_ID  
                                                                                 and (p.FLAG is null or p.FLAG <> '1') 
                                                                                 and p.UPDATE_TIME >= '2017/9/26 17:00:32' 
                                                                                 and p.UPDATE_TIME <= '2017/10/26 17:00:32') t1
                                                 left join T_DATA_PERSON_VEHICLE pv on t1.P_ID = pv.P_ID
                                                 ) t2 
              left join (select * from T_DATA_VEHICLE v  where 1=1) t4 on t2.VEHICLE_ID = t4.VEHICLE_ID 
             ) t
     ) x