Mysql - GROUP BY CUBE的等价物

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

I need to use the group by with cube, but apparently, it doesn't exists on Mysql ...

我需要使用带有立方体的组,但显然,它在Mysql上不存在...

Actually, I have to get all the possible combinations of a field ...
Something like ((10 + 30 + 8 + 42 + 3), (10 + 30 + 8 + 42), (10 + 30 + 8), (10 + 30), (10), (30 + 8 + 42 + 3), ...
See?

实际上,我必须得到一个场的所有可能的组合......像((10 + 30 + 8 + 42 + 3),(10 + 30 + 8 + 42),(10 + 30 + 8),( 10 + 30),(10),(30 + 8 + 42 + 3),......看?

Thanks in advance :) Have a good day

在此先感谢:)祝你有个美好的一天

1 个解决方案

#1

Months later... I can get you every distinct total, but that's about it.

几个月后......我可以得到你所有不同的总数,但就是这样。

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)