复杂的order By -子order by查询

Question

我需要对下面提到的表结构进行汇总计算，因为我需要按"sessionId“排序，并按"sequenceId”排序。特定会话可以具有一个或多个序列。每个序列从一个开始，然后按顺序向前移动。主键和flow-squence不能串联。

表>>

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进行测试。