MySQL递归选择查询结果集列数未知

Question

过去几个小时我一直在寻找解决方案。

这里有一些数据可以帮助解释这个问题:

mysql> describe REGION_FEATURE;
+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| ID                    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| REGION_ID             | int(10) unsigned | NO   | MUL | NULL    |                | 
| NAME                  | varchar(255)     | NO   |     | NULL    |                | 
| FILENAME_NOMENCLATURE | varchar(255)     | NO   |     | NULL    |                | 
| CONFIG_FILE_TYPE      | int(10) unsigned | NO   |     | NULL    |                | 
| ITERATOR_GROUP        | int(10) unsigned | NO   |     | NULL    |                | 
+-----------------------+------------------+------+-----+---------+----------------+

mysql> select * from REGION_FEATURE;
+----+-----------+----------------+-----------------------+------------------+----------------+
| ID | REGION_ID | NAME           | FILENAME_NOMENCLATURE | CONFIG_FILE_TYPE | ITERATOR_GROUP |
+----+-----------+----------------+-----------------------+------------------+----------------+
|  1 |         1 | MaxCpe01       | c01                   |                0 |              1 | 
|  2 |         1 | MaxCpe05       | c05                   |                0 |              1 | 
|  3 |         1 | PrivacyEnable  | pe                    |                0 |              2 | 
|  4 |         1 | PrivacyDisable | pd                    |                0 |              2 | 

我需要编写一个查询，以这种方式给出结果。我期望 4 行，但有 N 种组合。

具有相同 interator_group(I_G) 的行不会合并。在此示例中，I_G =1 x I_G=2，但可以有 N 个 I_G

I_G=1 x I_G=n x I_G=...


MaxCpe01 | PrivacyEnable

MaxCpe01 | PrivacyDisable

MaxCpe05 | PrivacyEnable

MaxCpe05 | PrivacyDisable

有人可以帮忙吗？

Answer 1

您正在寻找这样的东西吗？

SELECT t1.name name1, t2.name name2
  FROM region_feature t1 CROSS JOIN region_feature t2
 WHERE t1.iterator_group <> t2.iterator_group
   AND t1.id < t2.id
 ORDER BY name1;

输出:

|    NAME1 |          NAME2 ||----------|----------------|| MaxCpe01 | PrivacyDisable || MaxCpe01 |  PrivacyEnable || MaxCpe05 | PrivacyDisable || MaxCpe05 |  PrivacyEnable |

这里是SQLFiddle 演示