Mysql - 等同于 GROUP BY CUBE

Question

**ID** | **REL_ID** | **E**
   1   |      1     |   10
   2   |      1     |   30
   3   |      1     |    8
   4   |      1     |   42
   5   |      1     |   57

我需要将 group by 与 cube 一起使用，但显然，它在 Mysql 上不存在......

实际上，我必须得到一个字段所有可能的组合......
像 ((10 + 30 + 8 + 42 + 3), (10 + 30 + 8 + 42), (10 + 30 + 8), (10 + 30), (10), (30 + 8 + 42 + 3), ...
看到了吗？

提前致谢:)祝你有个美好的一天

Answer 1

几个月后......我可以得到每一个不同的总数，但仅此而已。

CREATE TABLE combos
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,REL_ID INT NOT NULL
,E INT NOT NULL
);

INSERT INTO combos VALUES
(1,1,10),
(2,1,30),
(3,1,8),
(4,1,42),
(5,1,57);

SELECT DISTINCT n1.e 
              + CASE WHEN n2.id IN(n1.id) THEN 0 ELSE n2.e END 
              + CASE WHEN n3.id IN(n2.id,n1.id) THEN 0 ELSE n3.e END 
              + CASE WHEN n4.id IN(n3.id,n2.id,n1.id) THEN 0 ELSE n4.e END 
              + CASE WHEN n5.id IN(n4.id,n3.id,n2.id,n1.id) THEN 0 ELSE n5.e END x
  FROM combos n1 
  JOIN combos n2 
    ON n2.id <= n1.id 
  JOIN combos n3 
    ON n3.id <= n2.id 
  JOIN combos n4 
    ON n4.id <= n3.id 
  JOIN combos n5 
    ON n5.id <= n3.id
 ORDER 
    BY x;

+-----+
| x   |
+-----+
|   8 |
|  10 |
|  18 |
|  30 |
|  38 |
|  40 |
|  42 |
|  48 |
|  50 |
|  52 |
|  57 |
|  60 |
|  65 |
|  67 |
|  72 |
|  75 |
|  80 |
|  82 |
|  87 |
|  95 |
|  97 |
|  99 |
| 107 |
| 109 |
| 129 |
+-----+
    25 rows in set (0.00 sec)