| | |
|---|
| | | |
|---|
| | | |
|---|
| | | <select id="getSyLackAlertList" resultMap="vo"> |
|---|
| | | SELECT |
|---|
| | | info.res_cd, |
|---|
| | | info.res_nm, |
|---|
| | | info.town_cd, |
|---|
| | | info.town_nm, |
|---|
| | | info.county_cd, |
|---|
| | | info.county_nm, |
|---|
| | | info.city_cd, |
|---|
| | | info.city_nm, |
|---|
| | | info.typeList as type |
|---|
| | | SELECT info.res_cd, |
|---|
| | | info.res_nm, |
|---|
| | | info.town_cd, |
|---|
| | | info.town_nm, |
|---|
| | | info.county_cd, |
|---|
| | | info.county_nm, |
|---|
| | | info.city_cd, |
|---|
| | | info.city_nm, |
|---|
| | | info.typeList as type |
|---|
| | | |
|---|
| | | FROM ( |
|---|
| | | SELECT |
|---|
| | | basic."guid" res_cd, |
|---|
| | | basic."name" res_nm, |
|---|
| | | basic.townCode town_cd, |
|---|
| | | basic.townName town_nm, |
|---|
| | | basic.countyCode county_cd, |
|---|
| | | basic.countyName county_nm, |
|---|
| | | basic.cityCode city_cd, |
|---|
| | | basic.cityName city_nm, |
|---|
| | | LISTAGG(basic.type, ',') WITHIN GROUP (ORDER BY basic.type ASC) typeList from ( |
|---|
| | | SELECT |
|---|
| | | distinct |
|---|
| | | arb."guid",arb."name", |
|---|
| | | stra.type, |
|---|
| | | case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode, |
|---|
| | | case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 3 THEN town."ad_code" |
|---|
| | | when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 3 THEN town."ad_name" |
|---|
| | | when county."ad_grad" = 3 THEN county."ad_name" END AS countyName, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 2 THEN town."ad_code" |
|---|
| | | when county."ad_grad" = 2 THEN county."ad_code" |
|---|
| | | ELSE city."ad_code" END AS cityCode, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 2 THEN town."ad_name" |
|---|
| | | when county."ad_grad" = 2 THEN county."ad_name" |
|---|
| | | ELSE city."ad_name" END AS cityName |
|---|
| | | FROM SJZT_MD."att_res_base" arb |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" town ON town."ad_code" = arb."interior_ad_guid" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" county ON county."ad_code" = town."p_ad_code" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" city ON city."ad_code" = county."p_ad_code" |
|---|
| | | LEFT JOIN YWXT.SM_TEXTUAL_RESEARCH_ATTACH STRA ON STRA.RES_GUID = arb."guid" and (stra.type = 2 or stra.type = 3 or stra.type = 8 or stra.type = 11 or stra.type = 12) |
|---|
| | | )basic |
|---|
| | | group by basic."guid",basic."name", |
|---|
| | | basic.townCode,basic.townName, |
|---|
| | | basic.countyCode,basic.countyName, |
|---|
| | | basic.cityCode,basic.cityName |
|---|
| | | )info |
|---|
| | | FROM (SELECT basic."guid" res_cd, |
|---|
| | | basic."name" res_nm, |
|---|
| | | basic.townCode town_cd, |
|---|
| | | basic.townName town_nm, |
|---|
| | | basic.countyCode county_cd, |
|---|
| | | basic.countyName county_nm, |
|---|
| | | basic.cityCode city_cd, |
|---|
| | | basic.cityName city_nm, |
|---|
| | | LISTAGG(basic.type, ',') WITHIN GROUP (ORDER BY basic.type ASC) typeList |
|---|
| | | from ( |
|---|
| | | SELECT |
|---|
| | | distinct |
|---|
| | | arb."guid", arb."name", |
|---|
| | | stra.type, |
|---|
| | | case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode, |
|---|
| | | case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 3 THEN town."ad_code" |
|---|
| | | when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 3 THEN town."ad_name" |
|---|
| | | when county."ad_grad" = 3 THEN county."ad_name" END AS countyName, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 2 THEN town."ad_code" |
|---|
| | | when county."ad_grad" = 2 THEN county."ad_code" |
|---|
| | | ELSE city."ad_code" END AS cityCode, |
|---|
| | | case |
|---|
| | | when town."ad_grad" = 2 THEN town."ad_name" |
|---|
| | | when county."ad_grad" = 2 THEN county."ad_name" |
|---|
| | | ELSE city."ad_name" END AS cityName |
|---|
| | | FROM SJZT_MD."att_res_base" arb |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" town ON town."ad_code" = arb."interior_ad_guid" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" county ON county."ad_code" = town."p_ad_code" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" city ON city."ad_code" = county."p_ad_code" |
|---|
| | | LEFT JOIN YWXT.SM_TEXTUAL_RESEARCH_ATTACH STRA ON STRA.RES_GUID = arb."guid" and (stra.type = 2 or stra.type = 3 or stra.type = 8 or stra.type = 11 or stra.type = 12) |
|---|
| | | ) basic |
|---|
| | | group by basic."guid", basic."name", |
|---|
| | | basic.townCode, basic.townName, |
|---|
| | | basic.countyCode, basic.countyName, |
|---|
| | | basic.cityCode, basic.cityName) info |
|---|
| | | where info.typeList != '2,3,8,11,12' or info.typeList is null |
|---|
| | | </select> |
|---|
| | | <select id="getWyLackAlertList" resultMap="vo"> |
|---|
| | | |
|---|
| | | SELECT |
|---|
| | | select |
|---|
| | | distinct |
|---|
| | | arb."guid" res_cd, |
|---|
| | | arb."name" res_nm, |
|---|
| | |
|---|
| | | when county."ad_grad" = 2 THEN county."ad_name" |
|---|
| | | ELSE city."ad_name" END AS city_nm, |
|---|
| | | stra.type |
|---|
| | | from sjzt_md."att_res_base" arb |
|---|
| | | from ( |
|---|
| | | select * from ( |
|---|
| | | select distinct "res_cd" from sjzt_md."att_wy_cd_base" awcb |
|---|
| | | ) a |
|---|
| | | left join sjzt_md."att_res_base" arb on (a."res_cd" = arb."guid") |
|---|
| | | ) arb |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" town ON town."ad_code" = arb."interior_ad_guid" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" county ON county."ad_code" = town."p_ad_code" |
|---|
| | | LEFT JOIN SJZT_MD."att_ad_base" city ON city."ad_code" = county."p_ad_code" |
|---|
