有如下表、字段
projects
idwork_order_costs
project_id, step_cost, hybrid_pipeline_typeproject_states
project_id, state, state_date, hybrid_pipeline_typesteps
id, stagework_orders
id, project_id, step_id, created_at, hybrid_pipeline_type
望查询得到的数据如下:
project_id step_cost state state_date created_at stage hybrid_pipeline_type
1 11 success 2015-10-09 2015-10-08 HAT pair
1 20 fail 2015-10-15 2015-10-12 AIT reagent
其中hybrid_pipeline_type可能的值为pair, reagent, null,根据work_order_costs表填的hybrid_pipeline_type值pair为一行,reagent为一行,若值为null都算pair的。created_at 为work_orders的id最大那条记录的created_at,stage为这条work_orders对应的step的stage
projects
idwork_order_costs
project_id, step_cost, hybrid_pipeline_typeproject_states
project_id, state, state_date, hybrid_pipeline_typesteps
id, stagework_orders
id, project_id, step_id, created_at, hybrid_pipeline_type
望查询得到的数据如下:
project_id step_cost state state_date created_at stage hybrid_pipeline_type
1 11 success 2015-10-09 2015-10-08 HAT pair
1 20 fail 2015-10-15 2015-10-12 AIT reagent
其中hybrid_pipeline_type可能的值为pair, reagent, null,根据work_order_costs表填的hybrid_pipeline_type值pair为一行,reagent为一行,若值为null都算pair的。created_at 为work_orders的id最大那条记录的created_at,stage为这条work_orders对应的step的stage
;with tb as
(
select
w.project_id, c.step_cost,
s.[state], s.state_date,
w.created_at, p.id,
isnull(w.hybrid_pipeline_type, 'pair') as hybrid_pipeline_type
from work_orders as w
left join project_states as s
on w.project_id = s.project_id
left join work_order_costs as c
on w.project_id = c.project_id
left join steps as p
on w.step_id = p.id
)select
max(project_id),
max(step_cost),
max([state]),
max(state_date),
max(created_at),
max(stage),
hybrid_pipeline_type
from tb
group by hybrid_pipeline_type
沒注意这帖是隐藏在MSSQL板块的MYSQL額
这个试试(聚合函数自己选, 也可以不用聚合,直接让系统自己去捡一个,MYSQL支持不完全聚合的分组):select
max(tb.project_id),
max(tb.step_cost),
max(tb.[state]),
max(tb.state_date),
max(tb.created_at),
max(tb.stage),
tb.hybrid_pipeline_type
from
(
select
w.project_id, c.step_cost,
s.[state], s.state_date,
w.created_at, p.id,
IFNULL(w.hybrid_pipeline_type, 'pair') as hybrid_pipeline_type
from work_orders as w
left join project_states as s
on w.project_id = s.project_id
left join work_order_costs as c
on w.project_id = c.project_id
left join steps as p
on w.step_id = p.id
) as tb
group by tb.hybrid_pipeline_type
你不提供点测试数据和结果, 我写了也没法测试确实我自己也发现没有表达清楚,数据如下:
projects
id
1
2work_order_costs
project_id, step_cost, hybrid_pipeline_type
1 1.1 pair
1 1.2 pair
1 1.3
1 1.4 reagent
1 1.5 reagent
2 1.6project_states
project_id, state, state_date, hybrid_pipeline_type
1 success 2015-10-09 pair
1 fail 2015-10-15 reagentsteps
id, stage
1 HAT
2 AITwork_orders
id, project_id, step_id, created_at, hybrid_pipeline_type
1 1 1 2015-10-01 10:15:01 pair
2 1 2 2015-10-02 10:15:02 pair
3 1 1 2015-10-03 10:15:03
4 1 1 2015-10-03 10:15:04 reagent
5 2 2 2015-10-03 10:15:05
望查询得到的数据如下(根据project_id、work_order_costs的hybrid_pipeline_type分行,step_cost项为求和;created_at取自work_orders表根据hybrid_pipeline_type分类后id最大的那条记录,stage为这条work_orders的step的stage——所有表里pair或null都算pair):
project_id step_cost state state_date created_at stage hybrid_pipeline_type
1 3.6 success 2015-10-09 2015-10-03 10:15:03 HAT pair
1 2.9 fail 2015-10-15 2015-10-03 10:15:04 HAT reagent
2 1.6 2015-10-03 10:15:05 AIT pair