如何选择一个外键与给定值列表完全匹配的主键？

Question

例如：

pk_ref    fk
======    ===
1         a
1         b
1         c
2         a
2         b
2         d

如何执行类似“伪”查询的查询：

select distinc pk_ref
where fk in all('a', 'c');

返回的查询结果必须与列表中外键的所有给定值匹配。

结果应该是：

1

而下面的select语句不能返回任何记录。

select distinc pk_ref
where fk in all('a', 'c', 'd');

我该怎么做？

Answer 1

尝尝这个

select pk_ref 
from yourtable 
group by pk_ref 
having count(case when fk = 'a',  then 1 end) >= 1 
and count(case when fk = 'c' then 1 end) >= 1

来动态地完成它。(考虑到您正在使用SQL SERVER)

创建拆分字符串函数并将输入作为逗号分隔值传递

Declare @input varchar(8000)= 'a,c',@cnt int 

set @cnt = len(@input)-len(replace(@input,',','')) + 1

select pk_ref 
from yourtable 
Where fk in (select split_values from udf_splitstring(@input , ','))
group by pk_ref 
having count(Distinct fk) >= @cnt 

您可以通过以下链接创建拆分字符串函数

https://sqlperformance.com/2012/07/t-sql-queries/split-strings

Answer 2

:list是输入列表(绑定变量)。length()返回值的差异在于绑定变量中的逗号数量。这个查询，或者其他非常接近它的查询，应该可以在几乎任何DB产品中工作。在Oracle中进行了测试。

select   pk_ref
from     tbl    --  enter your table name here
where    ',' || :list || ','   like   '%,' || fk || ',%'
group by pk_ref
having   count(distinct fk) = 1 + length(:list) - length(replace(:list, ',', ''))

Answer 3

如果您可以按设置传递IN运算符值，则可以执行以下操作

模式：

SELECT * INTO #TAB FROM (
SELECT 1 ID, 'a' FK
UNION ALL
SELECT 1, 'b'
UNION ALL
SELECT 1, 'c'
UNION ALL
SELECT 2, 'a'
UNION ALL
SELECT 2, 'b'
UNION ALL
SELECT 2, 'd'
UNION ALL
SELECT 1, 'a'
)AS A

使用CTE将'a‘、'c’设置为

;WITH CTE AS (
SELECT 'a' FK   --Here 'a','c' passed as a Set through CTE
UNION 
SELECT 'c'
)
,FINAL AS(

SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY (FK))AS COUNT_ID, ID, FK 
FROM #TAB where FK  IN (select FK FROM CTE)

)
SELECT ID FROM FINAL WHERE COUNT_ID>=(SELECT COUNT( FK) FROM CTE)