我有一個場景，其中參考表ref有一個具有不同值的列。我需要所有這些值在其他表中可用，你能幫忙查詢嗎？
ref桌子：
description
----------
abc
bcd
cdf
data桌子：
id | Description
-------------------
123| abc
123| bcd
123| cdf
124| abc
124| bcd
輸出應該是：
123| abc
123| bcd
123| cdf
我能夠獲得匹配的條目，但我只需要那些與所有值匹配的條目。
uj5u.com熱心網友回復：
SELECT id, description
FROM data d
WHERE not exists (select d.id, description from ref
except
select id, description from data)
https://dbfiddle.uk/?rdbms=oracle_21&fiddle=5150c253f9fb625bcca4bae27832554c
uj5u.com熱心網友回復：
您可以通過組合 DATA 表中每個 ID 的所有值來構建一個要加入的“鍵”，并將其與包含 REF 表中所有值的“鍵”進行比較。
WITH
data (id, description)
AS
(SELECT 123, 'abc' FROM DUAL
UNION ALL
SELECT 123, 'bcd' FROM DUAL
UNION ALL
SELECT 123, 'cdf' FROM DUAL
UNION ALL
SELECT 124, 'abc' FROM DUAL
UNION ALL
SELECT 124, 'bcd' FROM DUAL),
REF (description)
AS
(SELECT 'abc' FROM DUAL
UNION ALL
SELECT 'bcd' FROM DUAL
UNION ALL
SELECT 'cdf' FROM DUAL)
SELECT id, description
FROM (SELECT id,
description,
LISTAGG (description, '|')
WITHIN GROUP (ORDER BY description)
OVER (PARTITION BY id) AS djoinkey
FROM data d)
WHERE djoinkey =
(SELECT LISTAGG (description, '|') WITHIN GROUP (ORDER BY description) AS rjoinkey FROM REF);
ID DESCRIPTION
______ ______________
123 abc
123 bcd
123 cdf
uj5u.com熱心網友回復：
在資料中的唯一 ID 和參考值串列之間創建交叉連接，然后將該結果與實際資料左連接。如果匹配行數與行數相同，則該 id 具有每個參考值。
create table #ref (val nvarchar(3));
insert into #ref (val)
values
('abc'),
('bcd'),
('cdf');
create table #dat (id int, val nvarchar(3));
insert into #dat (id,val)
values
(123,'abc'),
(123,'bcd'),
(123,'cdf'),
(124,'abc'),
(124,'bcd');
SELECT #dat.id, #dat.val
FROM #dat
INNER JOIN (
SELECT ids_ref.id, COUNT(*) AS vals, SUM(CASE WHEN #dat.val IS NOT NULL THEN 1 ELSE 0 END) AS dat_vals
FROM (SELECT ids.id, #ref.val FROM (SELECT DISTINCT id FROM #dat) ids, #ref) ids_ref
LEFT JOIN #dat ON ids_ref.id = #dat.id AND ids_ref.val = #dat.val
GROUP BY ids_ref.id
) chk ON #dat.id = chk.id
WHERE chk.vals = chk.dat_vals;
drop table #ref;
drop table #dat;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422037.html
標籤：