例如,需要提取查询后各个ec下的前10条数据:
set @order_rank=0;
set @current_ec='1';
select * from (
select a.*,@order_rank:=if(@current_ec=a.ec,@order_rank+1,1) as order_rank,@current_ec:=a.ec from (
select DISTINCT cpi.city_name 地市,ci.city_name 区县, s.school_name 学校,ser.ec_code ec, gi.corp_name SA,gi.corp_code SA编码,
g.grade_name 年级,c.class_name 班级, upi.user_mobile 接收号码,sub.fee_mobile 订购号码, usd.student_name 学生姓名
from user_student_detail usd
inner join user_info ui on ui.user_id=usd.user_id and ui.is_deleted='UNDELETED'
inner join user_parent_detail upd on upd.child_id=usd.user_id and upd.is_deleted='UNDELETED'
inner join user_info upi on upi.user_id=upd.parent_id and upi.is_deleted='UNDELETED'
left join subscribe_info sub on sub.user_id=upi.user_id and sub.is_deleted='UNDELETED' AND sub.subscribe_status='SUBSCRIBE'
inner JOIN class_info c ON c.class_id = usd.class_id and c.is_deleted='UNDELETED'
LEFT JOIN school_term_system_grade_year_rel sr on sr.id=c.grade_rel_id and sr.is_deleted='UNDELETED'
LEFT JOIN school_grade_info g ON g.grade_id=sr.grade_id and g.is_deleted='UNDELETED'
left join school_year_info sy on sy.year_id=sr.year_id and sy.is_deleted='UNDELETED'
LEFT JOIN school_info s ON s.school_id = c.school_id and s.is_deleted='UNDELETED'
LEFT JOIN school_eccode_rel ser ON ser.school_id = s.school_id and ser.is_deleted='UNDELETED'
LEFT JOIN city_info ci ON s.city_code = ci.city_code
left join city_info cpi on cpi.city_code=ci.parent_code
LEFT JOIN corporation_school_rel csr ON csr.school_id = s.school_id AND csr.is_deleted='UNDELETED'
LEFT JOIN group_info gi ON csr.group_id = gi.id AND gi.is_deleted='UNDELETED'
WHERE sy.is_graduated='UNGRADUATED'
ORDER BY ser.ec_code
) a
) b where a.order_rank<=10
中间的查询sql不重要,重要的是
set @order_rank=0;
set @current_ec='1';
select * from (
select a.*,@order_rank:=if(@current_ec=a.ec,@order_rank+1,1) as order_rank,@current_ec:=a.ec from (
select DISTINCT cpi.city_name 地市,ci.city_name 区县, s.school_name 学校,ser.ec_code ec,
g.grade_name 年级,c.class_name 班级
FROM table
WHERE 1=1
ORDER BY ser.ec_code
) a
) b where a.order_rank<=10