mysql - 复杂的 order By - Suborder by Query

Question

我需要对下面提到的表结构执行汇总计算，因为我需要按“sessionId”排序，按“sequenceId”分组。特定 session 可以有一个或多个序列。每个序列都从一个开始，然后依次向前移动。主键和流序可能不串联。

表格>>

pk_id  session-id    sequence   some_other columns
  1    AAAAAAAA          1        blah-blah-blah
  2    AAAAAAAA          2        blah-blah-blah 
  3    AAAAAAAA          3        blah-blah-blah
  4    AAAAAAAA          2        blah-blah-blah
  5    AAAAAAAA          1        blah-blah-blah
  6    AAAAAAAA          3        blah-blah-blah
  7    AAAAAAAA          3        blah-blah-blah
  8    AAAAAAAA          2        blah-blah-blah
  9    AAAAAAAA          1        blah-blah-blah

我需要下单

 pk_id  session-id    sequence   some_other columns
  1    AAAAAAAA          1        blah-blah-blah
  2    AAAAAAAA          2        blah-blah-blah 
  3    AAAAAAAA          3        blah-blah-blah

  5    AAAAAAAA          1        blah-blah-blah
  4    AAAAAAAA          2        blah-blah-blah
  6    AAAAAAAA          3        blah-blah-blah

  9    AAAAAAAA          1        blah-blah-blah
  8    AAAAAAAA          2        blah-blah-blah
  7    AAAAAAAA          3        blah-blah-blah

如有任何帮助，我们将不胜感激。

Answer 1

假设您想将第一个 sequence=1 与第一个 sequence=2 和第一个 sequence=3 组合在一起，类似地第二个 1 与第二个 2 和第二个 3 等等(使用 pk_id 作为顺序)，你可以使用变量赋值给 sequence 值编号，然后使用生成的数字进行排序。

这就是我的意思:

SELECT
  pk_id,
  session_id,
  sequence,
  some_other_column
FROM (
  SELECT
    @row := (session_id = @sid AND sequence = @seq) * @row + 1 AS row,
    pk_id,
    @sid := session_id AS session_id,
    @seq := sequence AS sequence,
    some_other_column
  FROM
    atable,
    (SELECT @row := 0, @sid := '', @seq := 0) AS s
  ORDER BY
    session_id,
    sequence,
    pk_id
) AS s
ORDER BY
  session_id,
  row,
  sequence
;

这个查询可以测试at SQL Fiddle .