SQL where like in
兄弟姐妹们，
有个问题，请教下,在SQL中如下数据Table,
CodeName
201412ABC
203413BCD
201333CDE
203244DEF
209416FEG
207417EGH
205418GHI
202419HIJ
202420IJK
301412JKL
303413FZD
301333EFH
403244WTY
509416GYU
507417YTJ
605418EGP
602419DGU
602420QRE
我只用三个变量
declare @Code1 as nvarchar(20)
declare @Code2 as nvarchar(20)
declare @code3 as nvarchar(20)
我想实现，如果
情况1，中间任意一个变量填写值，那么就以这个为条件过滤
set @Code1 = 20
set @Code2 = ''
set @Code3 = ''
那将所有code中包含20
情况2，中间任意两个变量填写值，那么出现
set @Code1 = '20'
set @Code2 = '30'
set @Code3 = ''
那将所有code中包含20和30的数据
情况3，如果我的变量如下填写
set @Code1 = '201333'
set @Code2 = '203244'
set @Code3 = '40'
希望得到的结果是包含201333，203244和40的所有数据
情况4，都不填写值
set @Code1 = ''
set @Code2 = ''
set @Code3 = ''
所有数据都出来
请问SQL这个Where条件怎么写？ sql 数据 where in like
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-25 11:31:09
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[table]
if object_id('[table]') is not null drop table [table]
go
create table [table]([Code] int,[Name] varchar(3))
insert [table]
select 201412,'ABC' union all
select 203413,'BCD' union all
select 201333,'CDE' union all
select 203244,'DEF' union all
select 209416,'FEG' union all
select 207417,'EGH' union all
select 205418,'GHI' union all
select 202419,'HIJ' union all
select 202420,'IJK' union all
select 301412,'JKL' union all
select 303413,'FZD' union all
select 301333,'EFH' union all
select 403244,'WTY' union all
select 509416,'GYU' union all
select 507417,'YTJ' union all
select 605418,'EGP' union all
select 602419,'DGU' union all
select 602420,'QRE'
--------------开始查询--------------------------
declare @Code1 as nvarchar(20)=null
declare @Code2 as nvarchar(20)=null
declare @code3 as nvarchar(20)=NULL
DECLARE @sql VARCHAR(max)
SET @sql='
select * from [table]'
--IF @Code1 IS NOT NULL
--BEGIN
--SET @sql=@sql+'where code like '+'''%'+@code1+'%'''
--END
IF @code2 is NOT NULL AND @code1 is NOT NULL AND @code3 IS NOT NULL
begin
SET @sql=@sql+'where code like '+'''%'+@code1+'%''' +' or '+'code like '+'''%'+@code2+'%'''+' or '+'code like '+'''%'+@code3+'%'''
END
ELSE IF @code2 is NOT NULL AND @code1 is NOT NULL
BEGIN
SET @sql=@sql+'where code like '+'''%'+@code1+'%''' +' or '+'code like '+'''%'+@code2+'%'''
END
ELSE IF @code1 is NOT NULL
BEGIN
SET @sql=@sql+'where code like '+'''%'+@code1+'%'''
END
EXEC (@sql)
----------------结果----------------------------
/*
*/
create table gv
(Code nvarchar(10),Name nvarchar(10))
insert into gv
select N'201412', N'ABC' union all
select N'203413', N'BCD' union all
select N'201333', N'CDE' union all
select N'203244', N'DEF' union all
select N'209416', N'FEG' union all
select N'207417', N'EGH' union all
select N'205418', N'GHI' union all
select N'202419', N'HIJ' union all
select N'202420', N'IJK' union all
select N'301412', N'JKL' union all
select N'303413', N'FZD' union all
select N'301333', N'EFH' union all
select N'403244', N'WTY' union all
select N'509416', N'GYU' union all
select N'507417', N'YTJ' union all
select N'605418', N'EGP' union all
select N'602419', N'DGU' union all
select N'602420', N'QRE'
-- 建存储过程
create proc sp_gv
(@Code1 nvarchar(20),
@Code2 nvarchar(20),
@code3 nvarchar(20))
as
begin
declare @tsql nvarchar(4000)
select @tsql=N'select * from gv where '
+case when @Code1=N'' and @Code2=N'' and @Code3=N'' then N' 1=1 ' else N' 1=2 ' end
+case when @Code1<>N'' then N' or charindex('''+@Code1+N''',Code,1)>0 ' else N'' end
+case when @Code2<>N'' then N' or charindex('''+@Code2+N''',Code,1)>0 ' else N'' end
+case when @Code3<>N'' then N' or charindex('''+@Code3+N''',Code,1)>0 ' else N'' end
exec(@tsql)
end
--情况1，中间任意一个变量填写值，那么就以这个为条件过滤
exec sp_gv @Code1=N'20',@Code2=N'',@Code3=N''
/*
Code Name
---------- ----------
201412 ABC
203413 BCD
201333 CDE
203244 DEF
209416 FEG
207417 EGH
205418 GHI
202419 HIJ
202420 IJK
602420 QRE
(10 row(s) affected)
*/
--情况2，中间任意两个变量填写值，那么出现
exec sp_gv @Code1=N'20',@Code2=N'30',@Code3=N''
/*
Code Name
---------- ----------
201412 ABC
203413 BCD
201333 CDE
203244 DEF
209416 FEG
207417 EGH
205418 GHI
202419 HIJ
202420 IJK
301412 JKL
303413 FZD
301333 EFH
602420 QRE
(13 row(s) affected)
*/
--情况3，如果我的变量如下填写
exec sp_gv @Code1=N'201333',@Code2=N'203244',@Code3=N'40'
/*
Code Name
---------- ----------
201333 CDE
203244 DEF
403244 WTY
(3 row(s) affected)
*/
--情况4，都不填写值
exec sp_gv @Code1=N'',@Code2=N'',@Code3=N''
/*
Code Name
---------- ----------
201412 ABC
203413 BCD
201333 CDE
203244 DEF
209416 FEG
207417 EGH
205418 GHI
202419 HIJ
202420 IJK
301412 JKL
303413 FZD
301333 EFH
403244 WTY
509416 GYU
507417 YTJ
605418 EGP
602419 DGU
602420 QRE
(18 row(s) affected)
*/
