Блог

У меня есть таблица, подобная приведенной ниже —

 COL1    COL2
-------------
101     A
102     B
102     C
102     D
103     C
103     E
 

Мне нужно сгенерировать все возможные комбинации вместе с UniqueID для набора уникальных значений, COL1 показанных, как показано ниже, например, есть 3 уникальных значения COL1 , возможны 6 комбинаций, и поэтому в результате должно быть 18 строк. Количество уникальных значений может быть «n». Мне нужно динамическое решение, которое должно работать с любым количеством комбинаций и значений

 1,101,A
1,102,B
1,103,C
2,101,A
2,102,B
2,103,E
3,101,A
3,102,C
3,103,C
4,101,A
4,102,C
4,103,E
5,101,A
5,102,D
5,103,C
6,101,A
6,102,D
6,103,E
 

Пожалуйста, помогите и предложите ответ. Я пытался использовать ЛАГ, ОПЕРЕЖЕНИЕ, ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ, но не смог найти решение.

Ответ может быть с использованием любого из сценариев HANA SQL или Oracle SQL или MS-SQL.

Я придумал следующее решение, основанное на рекурсивном CTE, оконных функциях и арифметике.

 with
  a as (
    select 101 as col1, 'A' as col2
    union all select 102, 'B'
    union all select 102, 'C'
    union all select 102, 'D'
    union all select 103, 'C'
    union all select 103, 'E'
  ),
  b as (
    select
      col1, col2,
      count(*) over() as ct,
      count(*) over(partition by col1) as cc1,
      dense_rank() over(order by col1 desc) as rk1,
      row_number() over(partition by col1
                        order by col2) as rn12
    from a
  ),
  r as (
    select
      col1, col2, ct / cc1 as rq, ct / cc1 as ll, cc1, rk1, rn12
    from b
    union all
    select col1, col2, rq, ll - 1, cc1, rk1, rn12
    from r
    where ll > 1
  )
select
  iif(rk1 = 1, (ll - 1) * cc1   rn12, (rn12 - 1) * rq   ll) as id,
  col1, col2
from r
order by id, col1, col2
option (maxrecursion 0);
 

Вывод:

  ---- ------ ------ 
| id | col1 | col2 |
 ---- ------ ------ 
|  1 |  101 | A    |
|  1 |  102 | B    |
|  1 |  103 | C    |
|  2 |  101 | A    |
|  2 |  102 | B    |
|  2 |  103 | E    |
|  3 |  101 | A    |
|  3 |  102 | C    |
|  3 |  103 | C    |
|  4 |  101 | A    |
|  4 |  102 | C    |
|  4 |  103 | E    |
|  5 |  101 | A    |
|  5 |  102 | D    |
|  5 |  103 | C    |
|  6 |  101 | A    |
|  6 |  102 | D    |
|  6 |  103 | E    |
 ---- ------ ------ 
 

Попробуйте это на rextester.com для Microsoft SQL Server, Oracle и PostgreSQL.

 declare @t table (col1 int, col2 varchar(5));
insert into @t(col1, col2)
values
(101, 'A'), (101, 'B'), (101, 'C'), (101, 'D'),
(102, 'E'), (102, 'F'), (102, 'G'),
(103, 'H'), (103, 'I'), 
(104, 'J'), (104, 'K');


select t.combid, o.col1, o.col2
from 
(
    --number of combinations
    select agr.col1, agr.col2cnt, 
        --float-->decimal:truncate --> int
        cast(cast(exp(sum(log(agr.col2cnt)) over(order by agr.col1 desc)) as decimal(38,0)) as int)  as restcombs,     
        cast(cast(exp(sum(log(agr.col2cnt)) over()) as decimal(38,0)) as int)  as combsint
    from
    (
    --count of col2 per col1
    select col1, count(*) as col2cnt
    from @t
    group by col1
    ) as agr
) as dc
cross apply
(

    select jt.combid,
        1   (1 (jt.combid-1)/(dc.restcombs/dc.col2cnt)-1)%dc.col2cnt  as col2ordinal --ordinal of col2 within the combination            
    from
    (
    --just a tally, from 1 till combinations:=combsint
    select top (dc.combsint) row_number() over(order by @@spid) as combid
    from sys.all_objects as a
    cross join sys.all_objects as b
    cross join sys.all_objects as c 
    ) as jt
) as t
join
(
    --ordinal of col2 per col1
    select col1, col2, row_number() over(partition by col1 order by col2) as col2ordinal
    from @t
) as o on dc.col1 = o.col1 and t.col2ordinal = o.col2ordinal
order by t.combid, o.col1;