需求:
关联查询时,需要根据以上三个条件去查询:
1、预警来源,确定是从舆情上报表(F_YQSB)还是事件快报表(F_SJKB)中查;
2、预警对象,如果是舆情上报表(F_YQSB),则取TENDENCY='负面' and HEAT_VALUE>10;然后再根据预警对象(NAME_)对应的CLASSIFY字段,如果为1,那么再and PROVINCE_NAME='全区',如果是2 就and CONTENT_REFER=预警对象,如果是3 就and QU_XIAN=预警对象,如果是4 则 and PUBLIC_ONE=预警对象 ,如果是4 则 and PUBLIC_TWO=预警对象 ;
如果是事件快报表则 1=1 先取所有 ,再根据 CLASSIFY字段,1就1=1 取所有,2,3,4,5分别对应EVENT_LOCATION,QU_XIAN,PUBLIC_ONE,PUBLIC_TWO;
3、预警时间,查询舆情上报表(F_YQSB)或事件快报表(F_SJKB)的时间字段;
4、列表的内容为:事件标题(F_SJKB.EVENT_TITLE/F_YQSB.TITLE_)、属地单位(F_SJKB.EVENT_LOCATION/F_YQSB. CONTENT_REFER)、事件分类(F_SJKB.PUBLIC_ONE/F_YQSB.PUBLIC_ONE)、事件类别(F_SJKB.PUBLIC_TWO/F_YQSB.PUBLIC_TWO)、事件内容(F_SJKB.EVENT_DETAILS/F_YQSB.EVENT_OVERVIEW)、事件级别(F_SJKB.EMERGENCY_DEGREE/F_YQSB.HEAT_VALUE)、事件时间(F_SJKB.EVENT_TIME/F_YQSB.PUBLISH_TIME);
SELECT *
FROM (
SELECT *
FROM (select TITLE_ title,CONTENT_REFER location,PUBLIC_ONE one,PUBLIC_TWO two,EVENT_OVERVIEW detail,concat(HEAT_VALUE,'') degree,PUBLISH_TIME time from F_YQSB WHERE TENDENCY='负面' and HEAT_VALUE>10 and PUBLISH_TIME ='<#=@SJ#>' AND
case
when '<#=@CLASS#>'='1' and PROVINCE_NAME='全区' then 1
when '<#=@CLASS#>'='2' and CONTENT_REFER='<#=@name#>' then 1
when '<#=@CLASS#>'='3' and QU_XIAN='<#=@name#>' then 1
when '<#=@CLASS#>'='4' and PUBLIC_ONE='<#=@name#>' then 1
when '<#=@CLASS#>'='5' and PUBLIC_two='<#=@name#>' then 1
else 0
end =1
)t1
WHERE '<#=@source#>'='舆情事件'
UNION ALL
SELECT *
FROM (select EVENT_TITLE title,EVENT_LOCATION location,PUBLIC_ONE one ,PUBLIC_TWO two,EVENT_DETAILS detail,EMERGENCY_DEGREE degree,EVENT_TIME time from F_SJKB
where EVENT_TIME ='<#=@SJ#>' AND
case
when '<#=@CLASS#>'='1' then 1
when '<#=@CLASS#>'='2' and EVENT_LOCATION='<#=@name#>' then 1
when '<#=@CLASS#>'='3' and QU_XIAN='<#=@name#>' then 1
when '<#=@CLASS#>'='4' and PUBLIC_ONE='<#=@name#>' then 1
when '<#=@CLASS#>'='5' and PUBLIC_two='<#=@name#>' then 1
else 0
end =1
)t2
WHERE '<#=@source#>'='上报事件'
) AS result_table;