我刚刚开始学习MySQL,我遇到了这个问题,我非常需要解决方案或只是逻辑。
例如,我有这张表:
id a
-- --
1 1
2 2
3 3
4 5
5 6
6 7
7 9
8 10
9 11
10 12
现在,我想要的是显示第 a
列中的所有数据,这些数据应按系列分组。在这种情况下,结果应该是:
series_start|series_end|count
------------+----------+-----
1 3 3
5 7 3
9 12 4
这需要大量的子查询和连接。我现在还想不通。
这是一个间隙和孤岛问题,这是另一种解决它的方法,它也使用变量:
SELECT
MIN(a) AS series_start,
MAX(a) AS series_end,
MAX(a) - MIN(a) + 1 AS series_count
FROM (
SELECT
a,
@r := @r + 1 AS r
FROM
yourtable,
(SELECT @r := 0) AS x
ORDER BY
a
) s
GROUP BY
a - r
ORDER BY
a - r
;
这就是它的工作原理。
子查询将行号分配给表行,并返回以下行集:
a r
-- --
1 1
2 2
3 3
5 4
6 5
7 6
9 7
10 8
11 9
12 10
在这种情况下,存储行号的r
列恰好与数据示例中的id
列匹配,但我假设通常id
列可能有间隙,因此不能在此处使用。
主查询按 r
和 a
之间的差异对结果进行分组:对于顺序值,它将始终相同:
a r a - r
-- -- -----
1 1 0
2 2 0
3 3 0
5 4 1
6 5 1
7 6 1
9 7 2
10 8 2
11 9 2
12 10 2
这使我们能够将这些行组合在一起。此时剩下的就是获取最小值、最大值和计数,这将为您提供以下输出:
series_start series_end series_count
------------ ---------- ------------
1 3 3
5 7 3
9 12 4
可以在此处找到此查询的SQL Fiddle演示,我已经借用了@sgeddes的模式。
更新
由于不能使用数值变量(根据注释(,因此可以使用三角形自连接来分配行号,但它的效率远低于使用变量。无论如何,这是修改后的版本,对上一个查询的更改以粗体突出显示:
选择 MIN(a( 如series_start, 最大(a( 作为series_end, 最大 (a( - 最小 (a( + 1 作为 series_count从 ( 选择 数据.a, 计数(*( 作为 R 从 您的可伸缩 AS 数据 内部连接 您的计价 上 data.id>= tally.id 分组依据 数据.a+ 1分组依据 a - r订购方式 a - r;
方法本身保持不变:子查询返回一个排名行集,然后处理与以前相同的行集。
此处提供了修改后的查询的 SQL Fiddle 演示。
这是一个使用 user defined variables
的解决方案:
select min(series_start) series_start,
max(series_end) series_end,
1 + max(series_end) - min(series_start) count
from (
select t1.a series_start,
t2.a series_end,
@val:=IF(@prev=t2.a-1,@val,@val+1) val,
@prev:=t2.a
from yourtable t1
join yourtable t2 on t1.a = t2.a-1
join (select @val:= 0, @prev:= 0) t
order by t2.a
) t
group by val
- SQL 小提琴演示