select a.pd_listfile_dn, a.pd_option_1, pd_option_2, pd_option_3, pd_option_4, pd_option_5, count(f.table_idx) ,count(g.odr_idx) ,a.icon_best, a.icon_new, a.icon_sale, a.pd_state, a.pd_idx, a.pd_info, a.pd_oid_out, a.pd_state, a.pd_name, a.pd_price_consumer, a.pd_price_sell1, a.pd_price_sell2, a.pd_price_sell3, a.pd_listfile_dn, c.cg_idx from pd_product a left outer join pdc_category c ON c.pd_idx = a.pd_idx left outer join cg_category d ON d.cg_code4 = c.cg_idx left outer join table_101 f ON f.prodSeq = a.pd_idx and f.table_code='101030201' left outer join od_order_r g ON g.pd_idx = a.pd_idx and g.sub_state='11' where a.pd_idx and a.pd_where_pc='1' and ( d.cg_code4='167' or d.cg_code1='167' ) group by c.pd_idx order by pd_favor desc