SELECT
proj.PROJ_SNO projSno,
proj.name projName,
(select IFNULL(pr.`name`,"") from province pr where proj.PROVINCE_ID = pr.design) province,
(select IFNULL(tr.`NAME`,"") from trade tr where proj.TRADE_ID = tr.DESIGN) trade,
IFNULL(CONCAT('[',new.DEM_TASKNO,']',new.DEM_NAME),'') demName,
d.dept_brief deptBrief,
CASE t.PROJ_TYPE_ID
WHEN '4' THEN '維護專案' WHEN '22' THEN '維護專案' WHEN '51' THEN '維護專案'
WHEN '10' THEN '內部專案' WHEN '54' THEN '內部專案'
WHEN '9' THEN '研發專案'
WHEN '23' THEN '新需求' WHEN '40' THEN '新需求' WHEN '46' THEN '新需求'
WHEN '50' THEN '合作運營專案' WHEN '53' THEN '合作運營專案'
ELSE '工程專案'
END projType,
(select type_value from task_type_config where type_code='review_phase' and type_no = p.phase_id) phaseName,
if(p.rev_level=1,'一級','二級') reviewLevel,
IF(p.two_level_sign=2,'是','否') twoLevelSign,
to_char(plan_pass_date,'yyyy-MM-dd') planPassDate,
f.file_name fileName,
f.author,
f.author_slave authorSlave,
f.file_path filePath,
to_char(start_date,'yyyy-MM-dd') startDate,
to_char(p.oper_date,'yyyy-MM-dd') realPassDate,
a.rev_head revHead,
a.rev_person revPerson,
(select type_value from task_type_config where type_code='review_status' and type_no = p.rev_status) revStatus,
(select count(*) from review_act a1 where a1.plan_id = p.id and a1.rev_level = 1) oneLevelActNum,
(select count(*) from review_act a1 where a1.plan_id = p.id and a1.rev_level = 2) twoLevelActNum,
(select count(*) from review_opinion o,review_file f1 where o.doc_id = f1.id and o.plan_id = p.id and f1.rev_level = 1) oneLevelQuestionCount,
(select count(*) from review_opinion o,review_file f2 where o.doc_id = f2.id and o.plan_id = p.id and f2.rev_level = 2) twoLevelQuestionCount,
(select count(*) from review_opinion o where o.plan_id = p.id) questionCount,
(select sum(a1.plan_work) from review_act a1 where a1.plan_id = p.id and a1.rev_level = 1) oneLevelPlanWork,
(select sum(a1.plan_work) from review_act a1 where a1.plan_id = p.id and a1.rev_level = 2) twoLevelPlanWork,
IFNULL((select v.rev_templet from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.rev_templet from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) revTemplet,
IFNULL((select v.note from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.note from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) note,
IFNULL((select v.file_normal from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.file_normal from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) fileNormal,
IFNULL((select v.file_com from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.file_com from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) fileCom,
IFNULL((select v.show_link from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.show_link from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) showLink,
IFNULL((select v.method_use from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.method_use from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) methodUse,
IFNULL((select v.issue_score from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.issue_score from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) issueScore,
IFNULL((select v.rev_intel from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.rev_intel from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) revIntel,
IFNULL((select v.total_point from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 2),(select v.total_point from review_ves v where p.id = v.plan_id and v.doc_id = f.id and v.plan_level = 1)) totalPoint
FROM
review_plan p LEFT JOIN rm_new_demand new on p.dem_id = new.ID
LEFT JOIN review_file f on p.id = f.plan_id
LEFT JOIN review_act a on p.id = a.plan_id and a.act_order = 1 and a.rev_level = 1,
dept d,
proj LEFT JOIN proj_type t on proj.PROJ_TYPE_ID = t.PROJ_TYPE_ID
WHERE
p.proj_id = proj.proj_pk
AND p.dept_id = d.dept_id
--------------------------------------------------------這是mysql的陳述句-----------------------------------------
12張表，怎么轉換成hivee陳述句 表名一樣，就是要出的效果和mysql 一樣。怎么做。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76711.html
標籤：MySQL