创建所有可能组合的列表

I'm trying to do the following.

我正在尝试做以下事情。

I want to create a list of all possible relationships between certain things.

我想创建一个列出某些事物之间所有可能的关系。

For example. There's Mary, Alice, June, Cindy, Elizabeth, Betty, Jax

例如。有Mary，Alice，June，Cindy，Elizabeth，Betty，Jax

I would like to create all possible combinations for a list like this:

我想为这样的列表创建所有可能的组合：

Mary, Alice
玛丽，爱丽丝
Mary, June
玛丽，六月
Mary Cindy
玛丽辛迪
Mary, Jax
玛丽，贾克斯
Mary, Alice, June
玛丽，爱丽丝，六月
Mary, Alice, Cindy
玛丽，爱丽丝，辛迪
Mary, Alice, Elizabeth ...
玛丽，爱丽丝，伊丽莎白......
Mary, Alice, Jax
玛丽，爱丽丝，贾克斯
Mary, June, Cindy
玛丽，六月，辛迪
Mary, June, Elizabeth ...
玛丽，六月，伊丽莎白......
Mary, June, Jax
玛丽，六月，贾克斯
Mary, Cindy, Elizabeth
玛丽，辛迪，伊丽莎白
Mary, Cindy, Betty
玛丽，辛迪，贝蒂
Mary, Cindy, Jax ...
玛丽，辛迪，贾克斯......
Mary, Alice, June, Cindy
玛丽，爱丽丝，六月，辛迪
Mary, Alice, June, Elizabeth
玛丽，爱丽丝，六月，伊丽莎白
Mary, Alice, June, Betty ...
玛丽，爱丽丝，六月，贝蒂......
Mary, Alice, June, Cindy, Elizabeth
玛丽，爱丽丝，六月，辛迪，伊丽莎白
Mary, Alice, June, Cindy, Betty
玛丽，爱丽丝，六月，辛迪，贝蒂

Anyone know of a way to do this in either, SQL, Access, or C#? If there's another language out there that I can use on DB's I'd appreciate it a lot!

任何人都知道在SQL，Access或C＃中执行此操作的方法？如果还有另一种我可以在DB上使用的语言，我会非常感激！

Thanks, maria

谢谢，玛丽亚

5 个解决方案

#1

You may like recursive queries used by many modern DB servers for this.

您可能喜欢许多现代数据库服务器使用的递归查询。

ACCESS is not one of them :(

ACCESS不是其中之一:(

the following is a sample with postres

以下是postres的示例

postgres=# with RECURSIVE y1(b,c,d) as (
postgres(#      with x1(a) as (
postgres(#              values('a')
postgres(#              union all
postgres(#              values ('b')
postgres(#              union all
postgres(#              values ('c')
postgres(#              union all
postgres(#              values ('d')
postgres(#      )
postgres(#      select a,a,1
postgres(#      from x1
postgres(#      union all
postgres(#      select a||b,a,d+1
postgres(#      from x1
postgres(#              join y1 on (a < c)
postgres(# )
postgres-# select *
postgres-# from y1;
  b   | c | d
------+---+---
 a    | a | 1
 b    | b | 1
 c    | c | 1
 d    | d | 1
 ab   | a | 2
 ac   | a | 2
 ad   | a | 2
 bc   | b | 2
 bd   | b | 2
 cd   | c | 2
 abc  | a | 3
 abd  | a | 3
 acd  | a | 3
 bcd  | b | 3
 abcd | a | 4
(15 rows)


postgres=#

#2

SQL can be very good for this sort of thing. It gets sticky if you want to go very wide, but if you knew you wanted all combinations of up to, say, five items:

SQL对于这类事情非常有用。如果你想要走得很宽，它会变得很粘，但是如果你知道你想要所有组合，例如五个项目：

DECLARE @things TABLE (n nvarchar(50));

INSERT INTO @things (n) VALUES ('Mary'),('Alice'),('June'),('Cindy'),('Elizabeth'),('Betty'),('Jax'), (null);

SELECT 
  ISNULL(t1.n + ',', '') 
  + ISNULL(t2.n + ',', '') 
  + ISNULL(t3.n+ ',', '') 
  + ISNULL(t4.n+ ',', '') 
  + ISNULL(t5.n, '') 
FROM @things AS t1
JOIN @things AS t2 ON 1=1
JOIN @things AS t3 ON 1=1
JOIN @things AS t4 ON 1=1
JOIN @things AS t5 ON 1=1

#3

If you mean a power set, then you could use the following in C#

如果您的意思是电源设置，那么您可以在C＃中使用以下内容

public IEnumerable<IEnumerable<T>> GetPowerSet<T>(List<T> list)
{
    return from m in Enumerable.Range(0, 1 << list.Count)
           select
             from i in Enumerable.Range(0, list.Count)
             where (m & (1 << i)) != 0
             select list[i];
}

Usage:

用法：

var names = new List<string> { "Mary", "Alice", "June", "Cindy", "Elizabeth", "Betty", "Jax" };
var powerSet = GetPowerSet(names);
foreach (var nameCollection in powerSet)
{
  foreach (var name in nameCollection)
  {
    Console.Write(name);
  }
  Console.WriteLine();
}

You may want to remove any collections with less than 2 names, and also the complete set of names:

您可能希望删除名称少于2个的任何集合，以及完整的名称集：

var cleaned = powerSet.Where(nc => nc.Count() > 1 && nc.Count() < names.Count());

#4

If you have a defined number of elements, say , Mary, Alice, June, Cindy, Elizabeth, Betty, Jax, 8 elements in this case, you could nest 8 loops to display every possible permutation of elements. If the number is unknown you would need a recursive function.

如果你有定义数量的元素，比如Mary，Alice，June，Cindy，Elizabeth，Betty，Jax，在这种情况下有8个元素，你可以嵌套8个循环来显示元素的每个可能的排列。如果数字未知，则需要递归函数。

#5

Kind of awkward but will do the trick, or write an SQL proc with nested loops for each name.

有点尴尬，但会做的伎俩，或为每个名称写一个带有嵌套循环的SQL proc。

create a separate table for each name, example:

为每个名称创建一个单独的表，例如：

CREATE TABLE MARY(name VARCHAR(30));
INSERT INTO  MARY VALUES ("Mary");

{do the same for each different name and then}

SELECT * 
  FROM mary, 
       alice,
       june,
       cindy,
       elizabeth,
       betty,
       jax;

This will generate a Cartesian Product without duplicating any names!

这将生成笛卡尔积而不会复制任何名称！

#1