CREATE TABLE [dbo].[tb_1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [bianhao] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [col] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,    
    [jiedian] [int] NULL)
go
insert into tb_1 (bianhao,col,jiedian) 
select 2,'a1',1 Union all 
select 2,'a3',2 Union all 
select 2,'a3',1 Union all 
select 2,'a3',4 Union all
select 2,'a5',1 Union all 
select 2,'a8',2 Union all 
select 2,'a8',3  
go

select distinct col from tb_1 where bianhao=2 and col in('a1','a4') and jiedian=1 
Union all 
select case when not exists(select 1 from tb_1  where col='a1'  and bianhao=2) then 'a1' end as col 
Union all 
select case when not exists(select 1 from tb_1  where col='a4' and bianhao=2) then 'a4' end as col 

declare @s varchar(20),@sql varchar(8000)
set @s='a2,a8'
select @sql='select '''+replace(@s,',',''' as col union select ''')+''''

set @sql='select distinct a.col from ('+@sql+') a join tb_1 b 
on (not exists(select 1 from tb_1 where col=a.col and jiedian!=1) and a.col=b.col)
or not exists(select 1 from tb_1 where col=a.col)'

exec (@sql)

------解决方案--------------------

SQL code

declare @s varchar(100)
set @s='a1,a4'--参数在此替换
set @s=@s+','
;with cte as (
    select col=substring(@s,1,charindex(',',@s)-1),i=charindex(',',@s)
    union all
    select substring(@s,cte.i+1,charindex(',',@s,cte.i+1)-cte.i-1),i=charindex(',',@s,cte.i+1)
    from cte
    where charindex(',',@s,cte.i+1)>0
)
select a.col from cte a
full join tb_1 b on a.col=b.col
where (b.jiedian =1 and a.col is not null) or b.col is null