这么的SQL语句能不能简化

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

SQL code

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)goinsert 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 allselect 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,感觉不太好,请高手帮忙修改下

SQL code

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 

[解决办法]

SQL code

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 afull join tb_1 b on a.col=b.colwhere (b.jiedian =1 and a.col is not null) or b.col is null 

 

查看更多 下一篇