这样的SQL语句能不能简化?

[happydaily]

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

bianhao是固定的,就是每次查询都是固定的一个值,col取值范围 a1-a100,jiedian 取值范围 1-4.现在给定bianhao,和col,要求查出col有且jiedian=1或者col 不存在的col值是哪些.比如bianhao=2,col 为'a1','a4'时结果是'a1','a4';col为'a2','a8'时'a2'
我自己写了SQL,感觉不太好,请高手帮忙修改下

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 

[happydaily]

自己感觉不好的原因是,每增加一个col,我都得增加一个Union all select

[luckings]

没明白你说的什么意思，能否清楚点，看到你说的结果，没看到a4怎么出来的

[百年树人]

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)

[--小F--]

用动态SQL

[CAD233]

[Quote=引用 4 楼 的回复:]

用动态SQL
[/Quote]
如何使用动态SQL？求教。。。

[撩天槌]

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

[以学习为目的]

[Quote=引用 4 楼 的回复:]
用动态SQL
[/Quote]

小F美女写下

我去打篮球去