时间戳

首页 > 解决方案 > 带有子组顺序的Mysql顺序结果

sql - 带有子组顺序的Mysql顺序结果

问题描述

例如我有桌子

ID 参数1 参数2
1 C 1
2 一个 2
3 d 3
4 一个 1
5 b 3
6 一个 3
7 e 4

对 param1¶m2 是唯一的

我想得到这个结果，其中任何具有相同 param1 的项目在自己的子组中按 id 分组

ID 参数1 参数2
1 C 1
2 一个 2
4 一个 1
6 一个 3
3 d 3
5 b 3
7 e 4

任何建议如何做到这一点？

标签： sqlmariadb

解决方案

On the assumption that "groups" are ordered according to the MIN(ID) in the "group", and that rows within a "group" are ordered according to the id...

WITH
  summarised AS
(
  SELECT
    *,
    MIN(id) OVER (PARTITION BY param1) AS group_min_id
  FROM
    YourTable
) 
SELECT
  *
FROM
  summarised
ORDER BY
  group_min_id,
  id

Using correlated sub-query in place of window function...

SELECT
  *,
  (SELECT MIN(id) FROM yourTable AS lookup WHERE lookup.param1 = yourTable.param1) AS group_min_id
FROM
  yourTable
ORDER BY
  group_min_id,
  id

推荐阅读