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这两个参数一旦进行过滤就慢了？
