select * from(
select (select dep_name from department where dep_id= o.dep_id) depName,
out_remark ,sum(out_total_amount) total,out_date From out_warehouse o where
dep_id in (select dep_id from department)
Group By dep_id Having Count(*) > 1
ORDER BY dep_id
) t1
union all
select * from
( select (select dep_name from department where dep_id = o.dep_id) depName,
out_remark,sum(out_total_amount) total,out_date From out_warehouse o Group By dep_id Having Count(*) = 1
ORDER BY dep_id
) t2
union all
select * from (
select (select dep_name from department where dep_id = o1.dep_id) depName,out_remark,out_total_amount,out_date from out_warehouse o1 where dep_id in
( select dep_id From out_warehouse o where
dep_id in (select dep_id from department)
Group By dep_id Having Count(*) > 1
ORDER BY dep_id
) order by dep_id
) p1
union all
select * from (
select (select dep_name from department where dep_id = o1.dep_id) depName,out_remark,out_total_amount,out_date from out_warehouse o1 where dep_id not in
( select dep_id From out_warehouse o where
dep_id in (select dep_id from department)
Group By dep_id Having Count(*) > 1
ORDER BY dep_id
) order by dep_id
) p2
查询结果
depName out_remark total out_date
维修科 9号楼修漏 16.84 2015/8/28
供水科 xxx号井用 11.2 2015/8/26
门市部 马步村修漏用5.6 2015/8/28
维修科 9号楼修漏 3.08 2015/8/10
维修科 8号楼修漏 8.16 2015/8/16
维修科 10号楼修漏 5.6 2015/8/21
供水科 xxx号井用 5.6 2015/8/26
供水科 1号楼修漏 5.6 2015/8/28
门市部 马步村修漏用5.6 2015/8/29
需要实现效果是:
查询出总的金额 后面是 详细金额数据
求教sql语句 如何实现 和 优化 ?