Creating a list of all possible combinations

问题:

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

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!

Thanks, maria

回答1:

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

ACCESS is not one of them :(

the following is a sample with 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:

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#

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:

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.

回答5:

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

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!