mysql - mySQL 数据库中的分组和计算排序

Question

我有下表，其中包含 n 行数据(此处 n = 14):

id  |  a    |  b    |  c    |  d    |  e      -----> fields (id is the primary key)
+---+-------+-------+-------+-------+------
0   |  a0   |  b0   |  c0   |  d0   |  e0
1   |  a1   |  b1   |  c1   |  d1   |  e1
2   |  a2   |  b2   |  c2   |  d2   |  e2
3   |  a3   |  b3   |  c3   |  d3   |  e3
+---+-------+-------+-------+-------+------
4   |  a4   |  b4   |  c4   |  d4   |  e4
5   |  a5   |  b5   |  c5   |  d5   |  e5
6   |  a6   |  b6   |  c6   |  d6   |  e6
7   |  a7   |  b7   |  c7   |  d7   |  e7
+---+-------+-------+-------+-------+------
8   |  a8   |  b8   |  c8   |  d8   |  e8
9   |  a9   |  b9   |  c9   |  d9   |  e9
10  |  a10  |  b10  |  c10  |  d10  |  e10
11  |  a11  |  b11  |  c11  |  d11  |  e11
+---+-------+-------+-------+-------+------
12  |  a12  |  b12  |  c12  |  d12  |  e12
13  |  a13  |  b13  |  c13  |  d13  |  e13
+---+-------+-------+-------+-------+------   

现在考虑 m = 4(m 也可以有其他 +ve 整数值)。
我想按 id 的升序将数据分组到 m 行中，并按如下方式获取其余行:

id  |  a    |  b    |  c    |  d    |  e
+---+-------+-------+-------+-------+------
0   |  a0   |  b0   |  c0   |  d0   |  e0
1   |  a1   |  b1   |  c1   |  d1   |  e1
2   |  a2   |  b2   |  c2   |  d2   |  e2
3   |  a3   |  b3   |  c3   |  d3   |  e3
+---+-------+-------+-------+-------+------
4   |  a4   |  b4   |  c4   |  d4   |  e4
5   |  a5   |  b5   |  c5   |  d5   |  e5
6   |  a6   |  b6   |  c6   |  d6   |  e6
7   |  a7   |  b7   |  c7   |  d7   |  e7
+---+-------+-------+-------+-------+------
8   |  a8   |  b8   |  c8   |  d8   |  e8
9   |  a9   |  b9   |  c9   |  d9   |  e9
10  |  a10  |  b10  |  c10  |  d10  |  e10
11  |  a11  |  b11  |  c11  |  d11  |  e11
+---+-------+-------+-------+-------+------
12  |  a12  |  b12  |  c12  |  d12  |  e12
13  |  a13  |  b13  |  c13  |  d13  |  e13
+---+-------+-------+-------+-------+------  

然后我想要一个查询来获取以下数据:

idd  |  aa   |  bb             |  cc               |  dd   |  ee
+----+-------+-----------------+-------------------+-------+--------------+
0    |  a0   |  max(b0,..,b3)  |  min(c0,...,c3)   |  d3   |  (e0+...+e3)
1    |  a4   |  max(b4,..,b7)  |  min(c4,...,c7)   |  d7   |  (e4+...+e7)
2    |  a8   |  max(b8,..,b11) |  min(c8,...,c11)  |  d11  |  (e8+...+e11)
3    |  a12  |  max(b12,b13)   |  min(c12,c13)     |  d13  |  (e12+e13)
+----+-------+-----------------+-------------------+-------+--------------+  

我是 mySQL 数据库 (Ubuntu) 的新手，为了解决方案尝试了很多天。
如果有人向我展示一个 mySQL 查询如何实现它，那将非常有帮助。

对于 m = 5，查询应返回以下数据:

idd  |  aa   |  bb             |  cc               |  dd   |  ee
+----+-------+-----------------+-------------------+-------+--------------+
0    |  a0   |  max(b0,...,b4) |  min(c0,...,c4)   |  d4   |  (e0+...+e4)
1    |  a5   |  max(b5,...,b9) |  min(c5,...,c9)   |  d9   |  (e5+...+e9)
2    |  a10  |  max(b10,..,b13)|  min(c10,..,c13)  |  d13  |  (e10...+e13)
+----+-------+-----------------+-------------------+-------+--------------+ 

提前致谢。 . .

Answer 1

因为您已经有一个行为类似于行号的 id 列，要将 4 条记录分批分组，您只需聚合 id 除以 4，截断到一个整数。要查看这是如何工作的，id 值从 0 到 3(包括 3)将有一组 0，因为将这些值除以 4 得到小于 1。类似地，接下来的四个记录， id 值为 4 到 7，将有一组 1。

SELECT
    TRUNCATE(t1.id / 4, 0) AS idd,
    MAX(CASE WHEN t1.id = (SELECT MIN(t2.id) FROM yourTable t2
                           WHERE TRUNCATE(t2.id / 4, 0) = TRUNCATE(t1.id / 4, 0))
             THEN t1.a END) AS aa,
    MAX(t1.b) AS bb,
    MIN(t1.c) AS cc,
    MAX(CASE WHEN t1.id = (SELECT MAX(t2.id) FROM yourTable t2
                           WHERE TRUNCATE(t2.id / 4, 0) = TRUNCATE(t1.id / 4, 0))
             THEN t1.d END) AS dd,
    SUM(t1.e) AS ee
FROM yourTable t1
GROUP BY
    TRUNCATE(t1.id / 4, 0);

输出:

此处演示:

Rextester