大家给点优化意见

第八颗北斗星

下面是页面一个点击查询之后，后台执行的语句，我觉得单从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";
//                 转贴现买断

msnshow

那就从业务上来优化

PrefTest

什么数据库？一般需要从索引、表结构、SQL语句几个方面入手优化

云层

看到查询嵌套那么问题就来了，可以考虑中间过程用视图来解决或者你要考虑索引和设计了