mysql SQL:查找数组中具有相同索引的所有组合

kokeuurv  于 2023-03-11  发布在  Mysql
关注(0)|答案(1)|浏览(112)

我有下面的表在SQL:
| 身份证|向量|
| - ------|- ------|
| A0|一、二、十|
| A1|十一,十二,二十|
| A2|二十一,二十二,三十|
| A3|三十一,三十二,四十|
我想找到三个向量的所有组合,其中向量中的每个索引都与不同向量中的相同索引组合。例如,我想找到:
| 识别码1|身份2|id3|向量|
| - ------|- ------|- ------|- ------|
| A0|A1|A2|一、十一、二十一|
| A0|A1|A2|二、十二、二十二|
| A0|A1|A2|三、十三、二十三|
| ...|...|...|...|
| A1|A2|A3|十九、二十九、三十九|
| A1|A2|A3|二十、三十、四十|
我尝试了以下方法,但这会产生所有组合,并且没有考虑到数字的索引应该匹配,我不确定如何修复此问题。

SELECT * 
FROM (SELECT X.id, EXPLODE(X.vector) FROM vector X),
     (SELECT Y.id, EXPLODE(Y.vector) FROM vector Y),
     (SELECT Z.id, EXPLODE(Z.vector) FROM vector Z),
WHERE X.id < Y.id AND Y.id < Z.id;

我能想到的另一个解决方案是像这样将向量相加:
| 识别码1|身份2|id3|向量|
| - ------|- ------|- ------|- ------|
| A0|A1|A2|(一、二、十)、(十一、十二、二十)、(二十一、二十二、三十)|
然后我想转置向量,这样我就得到:
| 识别码1|身份2|id3|向量|
| - ------|- ------|- ------|- ------|
| A0|A1|A2|(一、十一、二十一)、(二、十二、二十二)、...、(十九、二十九、三十九)、(二十、三十、四十)|
但不幸的是SQL没有转置函数。

iyfjxgzm

iyfjxgzm1#

这个例子递归解析(拆分)字符串(用逗号值分隔)为对(id,value),而不使用STRING_SPLIT或EXPLODE。第二部分-通过索引连接值。
试验数据

create table test (id varchar(2),vector varchar(100));
insert into test values('A0','1,2,10');
insert into test values('A1','11,12,20');
insert into test values('A2','21,22,30');
insert into test values('A3','31,32,40');

查询中的递归CTE. vector列是可选的,仅为清楚起见

with recursive  r as(
select 1 i,id,vector
   ,case when length(vector)>0 and locate(',',vector,1)>0 
            then substring(vector,1,locate(',',vector,1)-1)
         when length(vector)>0 then vector
    end v
   ,locate(',',vector,1)+1 idx
   ,case when locate(',',vector,1)>0 then substring(vector,locate(',',vector,1)+1)
    else ''
    end rest
from test
union all 
select  i+1 i,id,vector
 ,case when length(rest)>0 and locate(',',rest,1)>0 
            then substring(rest,1,locate(',',rest,1)-1)
         when length(rest)>0 then rest
    end v
   ,locate(',',rest,1)+1 idx
   ,case when locate(',',rest,1)>0 then substring(rest,locate(',',rest,1)+1)
    else ''
    end rest
from r where length(rest)>0
)
, t as (-- select only necessary columns from recursive CTE
select i,id,v from r
)
select * 
  ,concat('(',t1.id,',',t2.id,',',t3.id,')') ids
  ,concat('(',t1.v,',',t2.v,',',t3.v,')') comb
from t t1 
left  join t t2 on t2.i=t1.i and t2.id>t1.id
left  join t t3 on t3.i=t2.i and t3.id>t2.id and t3.id>t1.id
where t1.i is not null and t2.i is not null and t3.i is not null

结果表
| 我|身份证|五|我|身份证|五|我|身份证|五|伊茨|梳|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|A0|1个|1个|A1|十一|1个|A2|二十一|(A0、A1、A2)|(一、十一、二十一)|
| 1个|A0|1个|1个|A1|十一|1个|A3|三十一|(A0、A1、A3)|(一、十一、三十一)|
| 1个|A0|1个|1个|A2|二十一|1个|A3|三十一|(A0、A2、A3)|(一、二十一、三十一)|
| 1个|A1|十一|1个|A2|二十一|1个|A3|三十一|(A1、A2、A3)|(十一、二十一、三十一)|
| 第二章|A0|第二章|第二章|A1|十二|第二章|A2|二十二|(A0、A1、A2)|(二、十二、二十二)|
| 第二章|A0|第二章|第二章|A1|十二|第二章|A3|三十二|(A0、A1、A3)|(二、十二、三十二)|
| 第二章|A0|第二章|第二章|A2|二十二|第二章|A3|三十二|(A0、A2、A3)|(二、二十二、三十二)|
| 第二章|A1|十二|第二章|A2|二十二|第二章|A3|三十二|(A1、A2、A3)|(十二、二十二、三十二)|
| 三个|A0|十个|三个|A1|二十个|三个|A2|三十|(A0、A1、A2)|(十、二十、三十)|
| 三个|A0|十个|三个|A1|二十个|三个|A3|四十|(A0、A1、A3)|(十、二十、四十)|
| 三个|A0|十个|三个|A2|三十|三个|A3|四十|(A0、A2、A3)|(十、三十、四十)|
| 三个|A1|二十个|三个|A2|三十|三个|A3|四十|(A1、A2、A3)|(二十、三十、四十)|
t2.id>t1.id这样的JOIN条件对于消除不必要的组合是必要的-包括组合(10,20,30)和排除(20,10,30)等等。
如果需要所有组合,请更改为t2.id<>t1.id

相关问题