不幸的是,MySQL没有窗口功能,这将是您需要的.所以你必须使用这样的东西:
最终查询
select data, group_row_number, overall_row_num
from
(
select data,
@num := if(@data = `data`, @num + 1, 1) as group_row_number,
@data := `data` as dummy, overall_row_num
from
(
select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r
) x
order by data, overall_row_num
) x
order by overall_row_num
说明:
首先,内部选择,这将应用一个mock row_number到表中的所有记录(参见SQL Fiddle with Demo):
select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r
查询的第二部分,将表中的每一行与下一行进行比较,以查看它是否具有相同的值,如果它不再启动group_row_number(参见SQL Fiddle with Demo):
select data,
@num := if(@data = `data`, @num + 1, 1) as group_row_number,
@data := `data` as dummy, overall_row_num
from
(
select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r
) x
order by data, overall_row_num
最后一次选择,返回所需的值,并按照您请求的顺序将它们放回:
select data, group_row_number, overall_row_num
from
(
select data,
@num := if(@data = `data`, @num + 1, 1) as group_row_number,
@data := `data` as dummy, overall_row_num
from
(
select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r
) x
order by data, overall_row_num
) x
order by overall_row_num