SELECT sum(d1.ITEM_DATA)bxpxf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '培训费%'
结果是 bxpxf=10000 SELECT sum(d1.ITEM_DATA)bxqcf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '汽车费%'
结果是 bxqcf=20000,2个SQL语句就like '内容%'不一样想用1条SQL语句得出结果
bxpxf bxqcf
10000 20000 这样的格式
用了 union all 没法得出这样的格式请问要怎么做啊。
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '培训费%'
结果是 bxpxf=10000 SELECT sum(d1.ITEM_DATA)bxqcf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '汽车费%'
结果是 bxqcf=20000,2个SQL语句就like '内容%'不一样想用1条SQL语句得出结果
bxpxf bxqcf
10000 20000 这样的格式
用了 union all 没法得出这样的格式请问要怎么做啊。
sum(IF(d2.ITEM_DATA like '汽车费%',d1.ITEM_DATA,0))d1.ITEM_DATA) bxqcf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027'
(
SELECT sum(d1.ITEM_DATA)bxpxf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '培训费%'
) a,
(
SELECT sum(d1.ITEM_DATA)bxqcf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '汽车费%') b ;
(
SELECT sum(d1.ITEM_DATA)bxpxf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '培训费%'
) a,
(
SELECT sum(d1.ITEM_DATA)bxqcf from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027' and d2.ITEM_DATA like '汽车费%') b ;
sum(case when d2.ITEM_DATA like '汽车费%' then d1.ITEM_DATA else 0 end) as bxqcf
from flow_run r
LEFT JOIN flow_run_data d1 on d1.RUN_ID=r.RUN_ID and d1.ITEM_ID='78'
LEFT JOIN flow_run_data d2 on d2.RUN_ID=r.RUN_ID and d2.ITEM_ID='801'
LEFT JOIN flow_run_data d6 on d6.RUN_ID=r.RUN_ID and d6.ITEM_ID='84'
where r.FLOW_ID='59' and d6.ITEM_DATA='p027'