主页 / 问题 / 970703
Accepted
Анатолий Эрнст
Asked:2020-04-17 16:47:56 +0000 UTC2020-04-17 16:47:56 +0000 UTC

不同尺寸集合的交集

  • 772

有 2 组记录具有相同的字段。但是行数不同。在这种情况下，第一组和第二组中都可以有更多的行。

必须输出所有行并在单独的列中放一个符号 - 1，如果这样的条目在第一组中，0 - 如果它不在第一组中（即它只在第二组中） . 记录的身份 - 通过所有字段的值的巧合。同时，按字段值总和的记录在一组内是唯一的。

也就是说，如何做这样的事情：

with aw (v1, v2, v3) as
(
  select 1, 2, 4 from dual
  union all
  select 1, 3, 4 from dual
  union all
  select 1, 3, 2 from dual
  union all
  select 5, 2, 4 from dual
  union all
  select 1, 7, 4 from dual
  union all
  select 1, 2, 6 from dual
),
bw (v1, v2, v3) as
(
  select 1, 2, 4 from dual
  union all
  select 1, 3, 40 from dual
  union all
  select 1, 30, 2 from dual
  union all
  select 5, 2, 4 from dual
  union all
  select 10, 7, 4 from dual
  union all
  select 1, 2, 6 from dual  
)
select 
  aw.v1, aw.v2, aw.v3,
  bw.v1, bw.v2, bw.v3,
  case when aw.v1 = bw.v1 and aw.v2 = bw.v2 and aw.v3 = bw.v3 then 1 else 0 end
from
aw
cross join
bw
sql

1 个回答

  1. Best Answer
    with aw (v1, v2, v3) as
(
  select 1, 2, 4 from dual
  union all
  select 1, 3, 4 from dual
  union all
  select 1, 3, 2 from dual
  union all
  select 5, 2, 4 from dual
  union all
  select 1, 7, 4 from dual
  union all
  select 1, 2, 6 from dual
),
bw (v1, v2, v3) as
(
  select 1, 2, 4 from dual
  union all
  select 1, 3, 40 from dual
  union all
  select 1, 30, 2 from dual
  union all
  select 5, 2, 4 from dual
  union all
  select 10, 7, 4 from dual
  union all
  select 1, 2, 6 from dual  
)
SELECT COALESCE(aw.v1, bw.v1) v1,
       COALESCE(aw.v2, bw.v2) v2,
       COALESCE(aw.v3, bw.v3) v3,
       CASE WHEN aw.v1 IS NULL THEN 'in bw'
            WHEN bw.v1 IS NULL THEN 'in aw'
            ELSE                    'in both' END in_where
FROM aw
FULL OUTER JOIN bw ON aw.v1=bw.v1 AND aw.v2=bw.v2 AND aw.v3=bw.v3

    在此处输入图像描述

    • 1

相关问题