mysql 去重复然后计数_MySQL – 获取每个重复值的计数器

不幸的是,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