oracle

select *
  from (select temp.docid,
               temp.doc_name,
               temp.parent_item_id,
               temp.dlcount,
               (select m.item_name
                  from sup_dict_item m
                 where m.item_id = temp.parent_item_id
                   and m.nls_lang = 'zh'
                   and m.item_status = 0) product_line,
               row_number() over(partition by temp.parent_item_id order by temp.dlcount desc) rn
          from (SELECT DL.DOCID,
                       DOC.DOC_NAME,
                       DT.ITEM_ID,
                       dt.parent_item_id,
                       dlcount
                  FROM (select l.docid, count(l.docid) dlcount
                          from (select docid 
from sup_dl_file_download_log
where downloadtime>=to_date('2011-07-01','YYYY-MM-DD')
and downloadtime<to_date('2011-08-01','YYYY-MM-DD') )l
                         group by l.docid) dl,
                       (select DISTINCT IM.ITEM_ID, im.parent_item_id
                          from sup_dict_item im
                         where im.classify_id = 'PRODUCT_FAMILY'
                           AND IM.ITEM_STATUS = 0) dt,
                       sup_kc_family_info fm,
                       SUP_KC_DOC_BASIC_INFO DOC
                 where fm.web_doc_id = dl.docid
                   AND DOC.WEB_DOC_ID = FM.WEB_DOC_ID
                   and dt.item_id = fm.product_family
                   and fm.ref_type = 0
                   and doc.language_type = 'zh') temp)
 where rn <= 10
 order by parent_item_id