一个表有个主键id,一个工号。现在工号重复,查出来所有的工号重复的数据,并对这些数据的工号后边加01,02,03
SELECT
rownu,
tb.jobid,
(rownum1 - d.minvalue)
FROM
(
SELECT
@rownum1 :=@rownum1 + 1 AS rownum1,
jobid
FROM
tablea,
(SELECT @rownum1 := 0) t
) tb
LEFT JOIN (
SELECT
min(rownum) minvalue,
jobid
FROM
(
SELECT
@rownum :=@rownum + 1 AS rownum,
jobid
FROM
tablea,
(SELECT @rownum := 0) t
) tc
GROUP BY
jobid
) d ON (tb.jobid = d.jobid);
效果如下图