SELECT
Proj. PROJ_SNO projSno,
Proj. Name projName,
(select IFNULL (pr. ` name `, "") from the province where pr proj. PROVINCE_ID=pr, design) province,
(select IFNULL (tr. ` NAME `, "") from the trade tr where proj. TRADE_ID=tr. DESIGN) trade,
IFNULL (CONCAT ('/', new DEM_TASKNO, ' ', new. DEM_NAME), ' ') demName,
D.d ept_brief deptBrief,
CASE t.P ROJ_TYPE_ID
WHEN '4' THEN 'maintenance projects' WHEN' 22 'THEN' maintenance projects' WHEN '51' THEN 'maintenance projects'
WHEN '10' THEN 'internal project' WHEN '54' THEN 'internal project'
WHEN a '9' THEN 'r&d projects'
WHEN '23' THEN 'new demand' WHEN '40' THEN 'new demand' WHEN '46' THEN 'new demand'
WHEN '50' THEN 'cooperation operations' WHEN' 53 'THEN' cooperation operations'
The ELSE 'project'
END projType,
(select type_value from task_type_config where type_code='review_phase' and type_no=p.p hase_id) phaseName,
If (p.r ev_level=1, 'level 1', '2') reviewLevel,
IF (p.t wo_level_sign=2, 'is',' no ') twoLevelSign,
To_char (plan_pass_date, 'yyyy - MM - dd) planPassDate,
F.f ile_name fileName,
F.a uthor,
F.a uthor_slave authorSlave,
F.f ile_path filePath,
To_char (the start_date, 'yyyy - MM - dd) startDate,
To_char (p.o per_date, 'yyyy - MM - dd) realPassDate,
A.r ev_head revHead,
A.r ev_person revPerson,
(select type_value from task_type_config where type_code='review_status' and type_no=p.r ev_status) revStatus,
(select count (*) from review_act a1 where a1. Plan_id=p.i d and a1. Rev_level=1) oneLevelActNum,
(select count (*) from review_act a1 where a1. Plan_id=p.i d and a1. Rev_level=2) twoLevelActNum,
(select count (*) from review_opinion o, where review_file f1 o.d oc_id=f1. Id and o.p lan_id=p.i d and f1. Rev_level=1) oneLevelQuestionCount,
(select count (*) from review_opinion o, review_file f2 where o.d oc_id=f2. Id and o.p lan_id=p.i d and f2. Rev_level=2) twoLevelQuestionCount,
(select count (*) from review_opinion o where o.p lan_id=p.i d) questionCount,
(select sum (a1. Plan_work) from review_act a1 where a1. Plan_id=p.i d and a1. Rev_level=1) oneLevelPlanWork,
(select sum (a1. Plan_work) from review_act a1 where a1. Plan_id=p.i d and a1. Rev_level=2) twoLevelPlanWork,
(select IFNULL (v.r ev_templet from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.r ev_templet from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) revTemplet,
IFNULL ((select v.n ote from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.n ote from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) note,
(select IFNULL (v. ile_normal from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v. ile_normal from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) fileNormal,
(select IFNULL (v. ile_com from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v. ile_com from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) fileCom,
(select IFNULL (v. how_link from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v. how_link from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) showLink,
IFNULL ((select v.m ethod_use from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.m ethod_use from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) methodUse,
IFNULL ((select v.i ssue_score from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.i ssue_score from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) issueScore,
(select IFNULL (v.r ev_intel from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.r ev_intel from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) revIntel,
IFNULL ((select v.t otal_point from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=2), (select v.t otal_point from review_ves v where p.i d=Valerie plame lan_id and v.d oc_id=f.i d and Valerie plame lan_level=1)) totalPoint
The FROM
Review_plan p LEFT JOIN rm_new_demand new on conviction yourself em_id.=new ID
LEFT the JOIN review_file f on p.i d=f.p lan_id
LEFT the JOIN review_act a on p.i d=Amy polumbo lan_id and a.a ct_order=1 and a.r ev_level=1,
D dept,
Proj LEFT JOIN proj_type t on proj. PROJ_TYPE_ID=t.P ROJ_TYPE_ID
WHERE
P.p roj_id=proj. Proj_pk
AND conviction yourself ept_id=d.d ept_id
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - this is a mysql statement -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

12 tables, how to convert hivee statement table name, is to the effect and mysql, how to do,