|
下面是页面一个点击查询之后,后台执行的语句,我觉得单从sql优化已经不现实了,大家给我点建议
下面只是截取的一部分,共有45个sql:
public List getData(Date beginDate, Date endDate, String brch_id) {
// -------------------买入业务start---------------------------
// 直贴
sql[0] ="select '直贴',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=1 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=1 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=1 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=1 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 票据包买
sql[1] ="select '票据包买',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=2 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=2 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=2 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=2 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 商票保贴
sql[2] ="select '商票保贴',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=3 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=3 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=3 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=3 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 代理贴现
sql[3] ="select '代理贴现',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=4 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=4 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=4 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=4 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 转贴现买断 |
|