单个表中的集合(按列分组) [英] Sets From a Single Table, Grouped By a Column

问题描述

我有一张桌子:

+-------+-------+----------+
| GROUP | State | Priority |
+-------+-------+----------+
|   1   |  MI   |     1    |
|   1   |  IA   |     2    |
|   1   |  CA   |     3    |
|   1   |  ND   |     4    |
|   1   |  AZ   |     5    |
|   2   |  IA   |     2    |
|   2   |  NJ   |     1    |
|   2   |  NH   |     3    |

And so on...

如何编写一个查询，使所有状态组按组按优先级顺序排列?像这样:

How do I write a query that makes all the sets of the states by group, in priority order? Like so:

+-------+--------------------+
| GROUP |        SET         |
+-------+--------------------+
|   1   | MI                 |
|   1   | MI, IA             |
|   1   | MI, IA, CA         |
|   1   | MI, IA, CA, ND     |
|   1   | MI, IA, CA, ND, AZ |
|   2   | NJ                 |
|   2   | NJ, IA             |
|   2   | NJ, IA, NH         |
+-------+--------------------+

这类似于我的问题，此处并且我试图修改该解决方案，但是，我只是一个40瓦的灯泡，这是一个60瓦的问题...

This is similar to my question here and I've tried to modify that solution but, I'm just a forty watt bulb and it's a sixty watt problem...

推荐答案

这个问题实际上比您链接的问题的答案简单，这是解决该问题的绝佳方法.但是，它使用相同的层次查询，即connect by

This problem actually looks simpler than the answer to the question you linked, which is an excellent solution to that problem. Nevertheless, this uses the same hierarchical queries, with connect by

如果priority始终是连续的数字序列，则可以使用

If it is the case that priority is always a continuous sequence of numbers, this will work

SELECT t.grp, level, ltrim(SYS_CONNECT_BY_PATH(state,','),',')   as "set"  
   from  t 
   start with priority = 1
 connect by   priority = prior priority + 1
            and grp = prior grp

但是，如果并非总是如此，我们将要求row_number()根据优先级顺序(不必是连续的整数)来定义序列

However, if that's not always true, we would require row_number() to define the sequence based on the order of priority ( which need not be consecutive integer)

with t2 AS
( 
  select t.*, row_number() 
        over ( partition by grp order by priority) as rn from t
)
SELECT t2.grp, ltrim(SYS_CONNECT_BY_PATH(state,','),',')   as "set"
   from  t2 
   start with priority = 1
 connect by   rn = prior rn + 1
            and grp = prior grp

演示

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