mysql - 如何对相同值的行求和，直到出现不同的值

Question

我有一张如图所示的 table 。我想按运算符(operator)查找最小分配日期和最小升级日期组，直到出现不同的运算符(operator)。前两个运算符的示例是相同的，所以我想将这两个运算符相加，因为它们之后是不同的运算符。

TROUBLETICKETINDEX         OPERATOR         Assign                   Escalate
    1262084                qbcca0110    12-12-2016 21:11:49  12-12-2016 21:11:49
    1262084                qbcca0110    12-12-2016 21:16:23  12-12-2016 21:16:23
    1262084                tss          12-14-2016 1:45:58   12-14-2016 1:45:58
    1262084                qbcca0110    12-14-2016 10:34:31  12-14-2016 10:34:31

请问在mysql中是否可行？

我正在尝试这个

  select th.TROUBLETICKETINDEX,
         th.OPERATOR, 
         min(th.HISTORYDATE) Assign, 
         max(th.HISTORYDATE) Escalate
    from troubleticketshistory th inner join 
         users_usergroups uug on uug.userId = th.TICKETOWNER inner join 
         usergroup ug on ug.userGroupId = uug.userGroupId
   where th.TROUBLETICKETINDEX=1262084
group by th.OPERATOR;

但这给了我

但我正在寻找

这是我的故障单历史记录表

HISTORYDATE         OPERATOR    TROUBLETICKETINDEX  TICKETOWNER USERGROUPINDEX
12-12-2016 21:11:49 qbcca0110   1262084                  24229  12016
12-12-2016 21:16:23 qbcca0110   1262084                  25111  22956
12-14-2016 1:45:58  tss         1262084                  24229  12016
12-14-2016 10:34:31 qbcca0110   1262084                  24229  12016

Answer 1

像这样的东西应该可以工作:

SELECT TROUBLETICKETINDEX,
       OPERATOR,
       MIN(HISTORYDATE) AS Assign,
       MAX(HISTORYDATE) AS Escalate
FROM (
   SELECT th.TROUBLETICKETINDEX,
          th.OPERATOR, 
          th.HISTORYDATE
          @rn := @rn + 1 AS rn,
          @grp := IF(@op = OPERATOR, @grp + 1,
                     IF(@op := OPERATOR, 1, 1)) AS grp
   FROM troubleticketshistory th
   CROSS JOIN (SELECT @rn := 0, @grp := 0, @op := '') AS vars
   WHERE th.TROUBLETICKETINDEX = 1262084
   ORDER BY th.Assign) AS t
GROUP BY TROUBLETICKETINDEX, OPERATOR, rn - grp 
ORDER BY Assign 

Demo here