一、查询语句如下:
select
group_id,
IF(@grps<>aa.group_id,@num:=1,@num:=@num+1) as total,
IF(@grps<>aa.group_id,@grps:=aa.group_id,@grps) as custom_type
from (
SELECT @num:=0,@grps:=-1) cc,
(
-- 目标分组表aa
select KeChengCode as group_id, grade from KeChengChengJiBiao where yeare='2019'
) as aa
order by group_id
二、说明:
1.@grps 为分组变量,num为分组组内行数变量
2.两个IF语句顺序不能更换,要查询自定义其他字段,可以在IF之前或之后增加。
3.order by 可以增加其他需要排序的字段,
4.目标分组表,可以是任意多个字段,必需指明一个按其分组的字段(group_id),为书写方便,建议直接 as 为group_id
三、举例
查询根据雇员的服务开始时间,在日历表中查询每个雇员的服务开始时间+10个工作日的日期:
select emp_id ,in_date, att_date from (
-- start 带分组编号查询
select IF(@grps<>aa.group_id,@num:=1,@num:=@num+1) as total,
IF(@grps<>aa.group_id,@grps:=aa.group_id,@grps) as custom_type,
aa.group_id as emp_id,aa.in_date,aa.att_date
from (
SELECT @num:=0,@grps:=-1) cc,
(
-- --start目标分组表aa
select emp_id as group_id ,in_date,a1.att_date
from emp_svr_info s1
join att_calendar a1
on s1.in_date <= a1.att_date and a1.iswork=1
-- --end 目标分组表aa
) as aa
order by group_id,att_date
-- end 带分组编号查询
) ee where total=10