单个表的组合，按列值分组 [英] Combinations from a Single Table, Grouped By a Column Value

问题描述

我有一个值表：

+-----+-------+
| ID  | STATE |
+-----+-------+
|  1  |   AL  |
|  1  |   AZ  |
|  1  |   MI  |
|  2  |   TX  |
|  2  |   TN  |
|  2  |   MO  |
|  2  |   ND  |
And many, many more...

我如何编写一个生成所有查询的查询每个ID的可能状态组合有哪些？因此结果如下：

How do I write a query that generates all of the possible state combinations for each individual ID? So the results are like:

+-----+-------------+
| ID  | COMBINATION |
+-----+-------------+
|  1  | AL, AZ      |
|  1  | AL, MI      |
|  1  | AZ, MI      |
|  1  | AL, AZ, MI  |
|  2  | TX, TN      |
|  2  | TX, MO      |
|  2  | TX, ND      |
|  2  | TN, MO      |
|  2  | TN, ND      |
  And so on...

感谢您的帮助！

编辑：Ravshan让我想到我不希望排列，但是，如果组合字符串按字母顺序排列，则可得到奖励。

Ravshan got me thinking that I do not want permutations but, bonus if the combination string is in alphabetical order.

推荐答案

您可以使用分层查询来执行此操作-这是使用旧式 connect by 进行的查询：

You can do this using hierarchical queries - here's one using the old-style connect by:

WITH your_table AS (SELECT 1 ID, 'AL' state FROM dual UNION ALL
                    SELECT 1 ID, 'AZ' state FROM dual UNION ALL
                    SELECT 1 ID, 'MI' state FROM dual UNION ALL
                    SELECT 2 ID, 'TX' state FROM dual UNION ALL
                    SELECT 2 ID, 'TN' state FROM dual UNION ALL
                    SELECT 2 ID, 'MO' state FROM dual UNION ALL
                    SELECT 2 ID, 'ND' state FROM dual UNION ALL
                    SELECT 3 ID, 'OH' state FROM dual)
SELECT ID,
       state,
       ltrim(SYS_CONNECT_BY_PATH(state, ','), ',') combinations,
       LEVEL
FROM   (SELECT id,
               state,
               count(*) OVER (PARTITION BY id) state_cnt
        FROM your_table)
WHERE  state_cnt = 1
OR     (state_cnt > 1 AND LEVEL > 1)
CONNECT BY PRIOR ID = ID
           AND PRIOR state < state
           AND PRIOR sys_guid() IS NOT NULL;

        ID STATE COMBINATIONS      LEVEL
---------- ----- ------------ ----------
         1 AZ    AL,AZ                 2
         1 MI    AL,AZ,MI              3
         1 MI    AL,MI                 2
         1 MI    AZ,MI                 2
         2 TX    TN,TX                 2
         2 TX    MO,TX                 2
         2 TN    MO,TN                 2
         2 TX    MO,TN,TX              3
         2 ND    MO,ND                 2
         2 TX    MO,ND,TX              3
         2 TN    MO,ND,TN              3
         2 TX    MO,ND,TN,TX           4
         2 TX    ND,TX                 2
         2 TN    ND,TN                 2
         2 TX    ND,TN,TX              3
         3 OH    OH                    1

必须使用connect by子句中的先前sys_guid（）不为空条件，以确保我们遍历co rrect行（如果您忽略它，结果将包含许多额外的行）。

The prior sys_guid() is not null condition in the connect by clause is required to ensure we're looping over the correct rows (if you were to omit it, the result would contain many extra rows).

我在列表中排除了只有一个状态的行输出-除非id仅列出单个状态。您可能会或可能不想在输出中包含单个状态，在这种情况下，可以完全删除谓词。

I've excluded rows with just a single state in the output - except if the id only listed a single state. You may or may not want to include single states in the output, in which case, you can remove the predicates entirely.

这篇关于单个表的组合，按列值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！