引用: 标量子查询
一个标量子查询是一个放在圆括弧里的普通 SELECT查询, 它只返回只有一个字段的一行.(参阅 Chapter 4 获取有关写查询的信息。) 该 SELECT 将被执行, 而其单个返回值将在周围的值表达式中使用. 把一个返回超过一行或者超过一列的查询用做标量查询是错误的. (不过,在特定的执行中,子查询不返回行则不算错误;标量结果认为是 NULL.)该子查询可以引用周围查询的变量, 那些变量也是在计算任意子查询的时候当做常量使用的. 又见 Section 6.15.
比如,下面的查询找出每个州中的最大人口数量的城市∶
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
FROM states;
子查询作为标量操作数--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 = 46
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 = 46
ps. 自己写的部分, 东西镶嵌在 left join 部分