| select dense_rank() over(order by b.prod_int_qty desc , b.prod_int_val desc , a.prod_cls_num ) as prod_rand , a.owner_id , sys_unit.unit_name , a.pmc_id , pmc.pmc_name , a.prod_cls_num , prod_cls_user.prod_name , prod_cls_user.description , prod_cls_user.full_name , prod_cls_user.edition , prod_cls_user.main_type , prod_cls_user.gender , prod_cls_user.year_val , prod_cls_user.std_unit_price , prod_cls_user.input_code , a.color_name , a.color_int_qty , a.color_int_val , b.prod_int_qty , b.prod_int_val from (select sys_unit.owner_id , ori.pmc_id , prod_cls.prod_cls_num , prod_color.color_name , sum(ori_dtl.int_qty) as color_int_qty, sum(ori_dtl.int_val) as color_int_val from ori, ori_dtl , sys_unit , product , prod_cls , prod_color where ori.unit_id = ori_dtl.unit_id and ori.ori_num = ori_dtl.ori_num and ori.unit_id = sys_unit.unit_id and ori_dtl.prod_id = product.prod_id and product.prod_cls_id = prod_cls.prod_cls_id and prod_color.color_id = product.color_id and ori.cancelled = 'F' group by sys_unit.owner_id , ori.pmc_id , prod_cls.prod_cls_num , prod_color.color_name) as a inner join (select sys_unit.owner_id , ori.pmc_id , prod_cls.prod_cls_num , sum(ori_dtl.int_qty) as prod_int_qty, sum(ori_dtl.int_val) as prod_int_val from ori, ori_dtl , sys_unit , product , prod_cls where ori.unit_id = ori_dtl.unit_id and ori.ori_num = ori_dtl.ori_num and ori.unit_id = sys_unit.unit_id and ori_dtl.prod_id = product.prod_id and product.prod_cls_id = prod_cls.prod_cls_id and ori.cancelled = 'F' group by sys_unit.owner_id , ori.pmc_id , prod_cls.prod_cls_num ) as b on a.owner_id = b.owner_id and a.pmc_id = b.pmc_id and a.prod_cls_num = b.prod_cls_num left join pmc on pmc.pmc_id = a.pmc_id and pmc.unit_id = a.owner_id left join prod_cls_user on prod_cls_user.prod_cls_num = a.prod_cls_num /*prod_cls_user为视图，因为需要调用太多表而且使用频率很高，所有就做了个视图*/ left join sys_unit on a.owner_id = sys_unit.unit_id where 1=1 ${if(len(pmc_name) == 0, "", "and pmc_name in ('" + pmc_name + "')")} ${if(len(unit_name) == 0, "", "and unit_name in ('" + unit_name + "')")} ${if(len(main_type) == 0, "", "and main_type in ('" + main_type + "')")} ${if(len(full_name) == 0, "", "and full_name in ('" + full_name + "')")} /*红色部分两个参数全部是通过视图来查询出来的,目前这两个参数查询的时候比不过滤还慢*/ order by b.prod_int_qty desc , b.prod_int_val desc , a.prod_cls_num 是不是因为视图的原因才出现main_type和full_name这两个参数一旦进行过滤就慢了？ |
最佳回答 |
||||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
|
0
|
|
|||
