mysql 分组查询top,mysql 求分组top

#mysql 求分组top

SELECT cdate,          #搜索日期 city_id, #城市id query_value, #搜索内容 has_result,     #搜索类型 1有结果 0无结果 pf_type,        #平台类型 1app端 2微信端 query_cnt_01_d, #T日搜索次数 query_cnt_02_d, #T-1日搜索次数 query_cnt_03_d, #T-2日搜索次数 query_cnt_07_d, #T-7日搜索次数 rank            #排名 FROM ( SELECT cdate, oe.city_id, oe.query_value, has_result, pf_type, oe.query_cnt_01_d,  oe.query_cnt_02_d, oe.query_cnt_03_d, oe.query_cnt_07_d, @rownum :=@rownum + 1, IF (@pcity = oe.city_id AND @has_result=oe.has_result AND @pf_type =oe.pf_type , @rank :=@rank + 1 ,@rank := 1) AS rank, #top @pcity := oe.city_id, @has_result := oe.has_result, @pf_type := oe.pf_type FROM ( SELECT t1.cdate,t1.has_result,t1.query_value,t1.pf_type,t1.city_id,t1.query_cnt AS query_cnt_01_d, IFNULL(t2.query_cnt,0) AS query_cnt_02_d,IFNULL(t3.query_cnt,0) AS query_cnt_03_d, IFNULL(t4.query_cnt,0) AS query_cnt_07_d  FROM (SELECT * FROM dw.t_rpt_user_search_rank_mid WHERE cdate=v_dDate) t1 LEFT JOIN (SELECT * FROM dw.t_rpt_user_search_rank_mid WHERE cdate=v_dDate1) t2  ON  t1.`city_id`=t2.`city_id` AND t1.`has_result`=t2.`has_result` AND t1.`pf_type`=t2.`pf_type` AND t1.`query_value`=t2.`query_value` LEFT JOIN (SELECT * FROM dw.t_rpt_user_search_rank_mid WHERE cdate=v_dDate2) t3 ON  t1.`city_id`=t3.`city_id` AND t1.`has_result`=t3.`has_result` AND t1.`pf_type`=t3.`pf_type` AND t1.`query_value`=t3.`query_value` LEFT JOIN (SELECT * FROM dw.t_rpt_user_search_rank_mid WHERE cdate=v_dDate7) t4 ON  t1.`city_id`=t4.`city_id` AND t1.`has_result`=t4.`has_result` AND t1.`pf_type`=t4.`pf_type` AND t1.`query_value`=t4.`query_value` ORDER BY t1.has_result ASC,t1.pf_type ASC,t1.city_id ASC,t1.query_cnt DESC )oe, ( SELECT @rownum := 0, @pcity := NULL , @has_result := NULL , @pf_type := NULL , @rank := 0 ) rk )m WHERE m.rank <= 50;