sql - PostgreSQL : check if value is in 2 columns and remove it from one of them

Question

我有两列 ind 和 tar，它们都包含数组。

 ind      tar
{10}      {10}
{6}       {5,6}
{4,5,6}   {5,6}
{5,6}     {5,6}
{7,8}     {11}
{11}      {5,6,7}
{11}      {8}
{9,10}    {6}

我想查找两个数组中是否都存在一个值，如果是这样，我只想将其保留在 ind 列中。例如，在第一行，两列中的值均为 10。我希望仅在 ind 列中得到该值，并将 tar 列留空。这是预期的结果:

 ind      tar      
{10}      
{6}       {5}
{4,5,6}   
{5,6}     
{7,8}     {11}
{11}      {5,6,7}
{11}      {8}
{9,10}    {6}

如何在 PostgreSQL 中做到这一点？

到目前为止，我只找到了共同的元素，但我不知道如何继续将它们仅保留在 ind 列中并从 tar 列中删除它们.

with t1 as (
select distinct ind, tar
from table_1
join table_2 using (id) 
limit 50
),
t2 as (
select ind & tar as common_el, ind , tar 
from t1
)
select *
from t2

结果如下:

   common_el   ind        tar
    {10}      {10}       {10}
    {6}       {6}       {5,6}
    {5,6}     {4,5,6}   {5,6}
    {5,6}     {5,6}     {5,6}

Answer 1

你可以这样做(fiddle):

表创建:

CREATE TABLE t(x INTEGER[], y INTEGER[]);

填充表格:

INSERT INTO t VALUES
('{10}',      '{10}'),
('{6}',       '{5,6}'),
('{4,5,6}',   '{5,6}'),
('{5,6}',     '{5,6}'),
('{7,8}',     '{11}'),
('{11}',      '{5,6,7}'),
('{11}',      '{8}'), 
('{9,10}',    '{6}'),

--
-- records below added for testing!
--

('{11}',      '{5,8,10,11,133}'),  
('{9,10}',    '{4,5,6,8,9,10,11}'),
('{9,10}',    '{4,5,6,8,9,10,11}');

标准数组，UNNEST + EXCEPT ( fiddle ):

如果您不想或不能，请使用 INTARRAY .

SELECT 
  t.x,
  ARRAY((SELECT UNNEST(t.y)) EXCEPT (SELECT UNNEST(t.x))) 
FROM 
  t;

结果:

x   array
{10}    {}
{6} {5}
{4,5,6} {}
{5,6}   {}
{7,8}   {11}
{11}    {7,5,6}
{11}    {8}
{9,10}  {6}
{11}    {8,10,133,5}
{9,10}  {11,8,5,4,6}
{9,10}  {11,8,5,4,6}

Et voilà - 想要的结果!请参阅here一个优秀的线程，其中包含许多解决此问题的方法以及讨论的密切相关的问题!