第一个选项是添加另一个存在的 where 子句.

First option would be to add another exist the where clause.

declare @fruitId1 int;
set @fruitId1=1;

declare @fruitId2 int;
set @fruitId2=3;

select *
from @Test
where
  BasketFruits.exist('/FRUITS/FID[.=sql:variable("@fruitId1")]')=1 and
  BasketFruits.exist('/FRUITS/FID[.=sql:variable("@fruitId2")]')=1

另一个版本是在 xquery 语句中使用这两个变量，计算点击次数.

Another version would be to use both variables in the xquery statement, counting the hits.

select * 
from @Test
where BasketFruits.value(
  'count(distinct-values(/FRUITS/FID[.=(sql:variable("@fruitId1"),sql:variable("@fruitId2"))]))', 'int') = 2

如果您知道在编写查询时将使用多少 FID 参数，上面的两个查询就可以正常工作.如果您处于 FID 数量不同的情况，您可以使用类似的方法.

The two queries above will work just fine if you know how many FID parameters you are going to use when you write the query. If you are in a situation where the number of FID's vary you could use something like this instead.

declare @FIDs xml = '<FID>1</FID><FID>3</FID>'

;with cteParam(FID) as
(
  select T.N.value('.', 'int')
  from @FIDs.nodes('FID') as T(N)
)  
select T.BasketName
from @Test as T
  cross apply T.BasketFruits.nodes('/FRUITS/FID') as F(FID)
  inner join cteParam as p
    on F.FID.value('.', 'int') = P.FID
group by T.BasketName
having count(T.BasketName) = (select count(*) from cteParam)
 

将@FIDs 变量构建为 XML 以保存要在查询中使用的值.

Build the @FIDs variable as an XML to hold the values you want to use in the query.

您可以在这里测试最后一个查询:https:///data.stackexchange.com/***/q/101600/relational-division-with-xquery

You can test the last query here: https://data.stackexchange.com/***/q/101600/relational-division-with-xquery