子查詢作為標量操作數--MySql數據庫
ps: 這種方法的只能查詢單一值
《Oracle 高效設計》 讀書思考--標量子查詢查詢性能討論
能提供不少思路
select n.ex_name, c.customer_name, n.remark, e.employee_name as create_name, n.create_user, n.project_id, n.id as node_id, n.model_id, n.file_id, g.grade_name as level_name, t.type as type_name,
exe.exe_ids, exe.exe_names, dept.dept_ids, dept.dept_names,
Convert(DATE_FORMAT(pn.node_end_time, '%Y年%c月%e日') USING utf8) as end_time
from hrp_project_execution n
left join hrp_cycle_grade g on g.id = n.worklevel
left join hrp_work_type t on t.id = n.worktype
left join hrp_customer c on c.id = n.customer_id
left join hrp_company_employee_info e on e.id = n.create_user
left join hrp_project_node pn on pn.id = n.node_id
left join (select ne.execution_id, Convert(GROUP_CONCAT(ne.executor_id) USING utf8) as exe_ids, GROUP_CONCAT(e.employee_name) as exe_names
from hrp_project_execution_executor ne
inner join hrp_company_employee_info e on e.id = ne.executor_id
where ne.delete_flag = 0 and ne.execution_id = 46 group by ne.execution_id
) exe on exe.execution_id = n.id
left join (select de.exectuion_id, Convert(GROUP_CONCAT(de.deptor_id) USING utf8) as dept_ids, GROUP_CONCAT(e.employee_name) as dept_names
from hrp_project_execution_deptor de
inner join hrp_company_employee_info e on e.id = de.deptor_id
where de.delete_flag = 0 and de.exectuion_id = 46 group by de.exectuion_id
) dept on dept.exectuion_id = n.id
where n.id = 46posted on 2009-07-15 12:47
黃小二 閱讀(1912)
評論(0) 編輯 收藏 所屬分類:
[DB].Oracle 、
[DB].MySQL 、
[DB].SQL Server