select a.*,b.item_name from (select sum(buy_num) num,nqh from (select substr(a.embed_dis_province,1,10) dtime,b.buy_num,case substr(a.idcardcode,1,2) when '11' then '1100' when '12' then '1200' when '31' then '3100' when '50' then '5000' else substr(a.idcardcode,1,4) end as nqh from f_dd_distri_order a,f_dd_order_products b where 1=1 and a.order_id=b.order_id and a.dis_order_state='100' AND a.CREATE_DATE >='2016-10-01 00:00:00' AND a.CREATE_DATE <='2016-11-23 23:59:59' ) a group by nqh) a,(select item_name,substr(a.item_code,1,4) code from xt_dictionary_item a where type_id=25 and length(format_code)<=20 and not exists(select 1 from xt_dictionary_item b where type_id=(SELECT xt.TYPE_ID FROM xt_dictionary_type xt WHERE xt.DIC_CODE='GB/T_2260_2013') and substr(b.item_code,1,3) in('110','120','310','500') and length(b.format_code)=20 and a.item_id=b.item_id )) b where a.nqh=b.code order by num desc
