mysql - 在MySQL中保持重复记录的连续运行

曾经有很多类似的问题问到，但我找不到一种方法来准确地完成我想做的事情。我需要在列中保持重复条目的连续计数，并在“所有者编号”列中报告该编号。下面的例子：

Roll Number  |  Owner Name  |  Owner Number
000001       |  Patrick H.  |  1
000001       |  Paula H.    |  2
000002       |  Fred R.     |  1
000003       |  Chris P.    |  1
000003       |  Kayla A.    |  2
000003       |  Phil J.     |  3

换句话说，我只需要计算每个卷到那时为止有多少个所有者，而不仅仅是每个卷的所有者总数。任何帮助将不胜感激。

最佳答案

您可以使用一两个子查询和一个@variable来执行此操作

即http://sqlfiddle.com/#!2/7c2fa/7

SELECT

  -- Re-selected at this level to tidy up the calculation fields
  f.rollNumber,
  f.ownerName,
  f.ownerNumber

FROM (
  SELECT

    d.rollNumber,
    d.ownerName,
    -- If the current roll number equals the previous, increment, else reset to 1
    @i := IF(d.rollNumber=@last,@i+1,1) AS ownerNumber,
    -- Sets @last to be == the "current" one for use on next row
    @last := d.rollNumber

  FROM
  (
      -- Get the raw data ordered by roll number
      SELECT

          LPAD(rollNumber,6,'0') rollNumber,
          ownerName

      FROM someTable
      -- Reorder / filter as much as you like, but always rollNumber ASC first
      ORDER BY
          rollNumber ASC,
          ownerName ASC
  ) d,(
      -- Set up the variables we will need shortly
      SELECT @i:=0,@last:=0
  ) v
) f;