两次CROSS APPLY致重复结果，求无需DISTINCT的解决办法

问题场景 #

现有XYZ表数据如下：

Id  Name   ssids
1    a1    1,2
2    a2    3
3    a3    4
4    a4    5

执行以下SQL查询，试图通过两次CROSS APPLY拆分ssids字段和参数值并关联，结果出现重复记录，且不想使用DISTINCT去重：

declare @Paravalue varchar(500)='1,2,3'

select t.*
from XYZ  t
cross apply string_split(t.ssids, ',') ColumnIds
cross apply string_split(@Paravalue, ',') paraId
where paraId.value = ColumnIds.value 

当前输出（存在重复） #

Id  Name   ssids
 1    a1    1,2
 1    a1    1,2
 2    a2    3

期望输出（无重复，不使用DISTINCT） #

Id  Name   ssids
1    a1    1,2
2    a2    3

问题原因 #

两次CROSS APPLY会生成笛卡尔积：

• 对于Id=1的记录，string_split(t.ssids, ',')拆分出2行（值为1、2）；string_split(@Paravalue, ',')拆分出3行（值为1、2、3）。
• 过滤匹配后，会得到2条匹配结果（1匹配1、2匹配2），因此这条记录会被输出2次，最终导致重复。
• 实际需求是只要记录的ssids中存在任意一个值在参数列表内，就保留该记录一次，而非每个匹配值都输出一次。

解决方案 #

方案1：使用EXISTS子查询（推荐，性能更优） #

通过EXISTS判断当前记录是否存在匹配的拆分值，只要存在就返回该记录一次：

declare @Paravalue varchar(500)='1,2,3'

select t.*
from XYZ t
where exists (
    select 1
    from string_split(t.ssids, ',') ColumnIds
    where ColumnIds.value in (select value from string_split(@Paravalue, ','))
)

方案2：单次CROSS APPLY结合GROUP BY #

通过GROUP BY将同一记录的多个匹配结果合并为一条，效果等同于去重但无需使用DISTINCT：

declare @Paravalue varchar(500)='1,2,3'

select t.Id, t.Name, t.ssids
from XYZ t
cross apply string_split(t.ssids, ',') ColumnIds
where ColumnIds.value in (select value from string_split(@Paravalue, ','))
group by t.Id, t.Name, t.ssids

方案3：CROSS APPLY结合TOP 1 #

通过子查询判断是否存在匹配，只要存在就返回一个标记，从而确保主表记录只输出一次：

declare @Paravalue varchar(500)='1,2,3'

select t.*
from XYZ t
cross apply (
    select top 1 1
    from string_split(t.ssids, ',') ColumnIds
    inner join string_split(@Paravalue, ',') paraId 
        on ColumnIds.value = paraId.value
) as MatchCheck

内容的提问来源于stack exchange，提问作者user3754858