计算视图中的字段

Question

我有下表"数字"；在我的MariaDB数据库中：

id	数字
1	25
2	41
3	3
4	73
5	38
6	41
7	12
8	14

Yo可以按数字分组，并使用COUNT()聚合函数获得列occurrences，使用MAX()窗口函数获取列IDdifferences:

SELECT number,
COUNT(*) occurrences,
MAX(MAX(id)) OVER () - MAX(id) IDdifferences
FROM numbers
GROUP BY number
ORDER BY number;

如果ID之间存在间隙：

SELECT number,
COUNT(*) occurrences,
MAX(MAX(rn)) OVER () - MAX(rn) IDdifferences
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM numbers) t
GROUP BY number
ORDER BY number;

请参阅演示