两圈之间的时间

Question

除了1点（在第三个值时，我需要上一次），这是表（时间）模式、输出和SQL:之外，我的SQL工作很好

id        number    time
-----------------------------------
1         9        00:00:10.000000
2         10       00:00:15.000000
3         9        00:00:22.000000
4         10       00:00:35.000000
1         9        00:00:55.000000
SELECT t.number, COUNT(1) laps, 
GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
  FROM (
        SELECT t1.id, t1.number, 
        TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
        FROM times t1 
  LEFT JOIN times t2 ON t1.number = t2.number AND t2.id < t1.id
GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

输出结果：

number    laps     times
-----------------------------------
9         3        00:00:10,00:00:12,00:00:23
10        2        00:00:15,00:00:20

我需要这个预期的结果：

number    laps     times
-----------------------------------
9         3        00:00:10,00:00:12,00:00:33
10        2        00:00:15,00:00:20

其中第三次-previos计算时间，在这种情况下（00:00:10,00:00:12，00:00:23-00:00:12）我尝试了，但对之前的所有时间求和。

SQL Fiddle

Answer 1

只需要将SUM更改为MAX

SELECT t.number, COUNT(1) laps, 
  GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
FROM (
  SELECT t1.id, t1.number, 
    TIME_TO_SEC(t1.time) - COALESCE(MAX(TIME_TO_SEC(t2.time)), 0) time
  FROM times t1 
  LEFT JOIN times t2 ON t1.number = t2.number AND t2.time < t1.time  -- Suggest you don't use id to compare
  GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

请参阅SQL FIDDLE演示

这是另一个没有内部分组依据的版本。它们具有相同的效果，但不同的查询计划。因此，您可以选择比较性能。

SELECT t.number, COUNT(1) laps, 
  GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.time) times
FROM (
  SELECT t1.id, t1.number, t1.time tm1,
    (SELECT  TIME_TO_SEC(t1.time) - COALESCE(MAX(TIME_TO_SEC(t2.time)),0) 
       FROM times t2 
      WHERE t1.number = t2.number AND t2.time < t1.time) AS time
  FROM times t1 
) t
GROUP BY number;

Answer 2

这有点棘手，但这里有一个解决方案：

主要问题是加入中的"小于"条件。因此，首先你必须根据这个答案将一个连续的数字（排名）添加到你的群组中。然后你就可以按这个等级加入。生成的查询有点大，但会给您所需的结果：

SELECT t.number, COUNT(1) laps,
GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
FROM (
    SELECT t1.id, t1.number,
        TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
    FROM
    (SELECT
        number, id, time,
        (
            CASE number
            WHEN @curType
            THEN @curRow := @curRow + 1
            ELSE @curRow := 1 AND @curType := number END
        ) AS rank
    FROM times, (SELECT @curRow := 0, @curType := '') r
    ORDER BY  number, id
    ) as t1
    LEFT JOIN
    (SELECT
        number, id, time,
        (
            CASE number
            WHEN @curType
            THEN @curRow := @curRow + 1
            ELSE @curRow := 1 AND @curType := number END
        ) AS rank
    FROM times, (SELECT @curRow := 0, @curType := '') r
    ORDER BY  number, id
    ) t2
    ON t1.number = t2.number and t2.rank+1 = t1.rank
    GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

给出：

| NUMBER | LAPS |                      TIMES |
|--------|------|----------------------------|
|      9 |    3 | 00:00:10,00:00:12,00:00:33 |
|     10 |    2 |          00:00:15,00:00:20 |