22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int,n varchar(10),page int)
insert into tb select 1,'aa',5
insert into tb select 1,'bb',3
go
declare @n varchar(10),@p int
--set @n='aa'
set @p=5
--下面是你要的一句sql语句
select * from tb where 1=(case when @n is null or n=@n then 1 else 0 end) and 1=(case when @p is null or page=@p then 1 else 0 end)
go
drop table tb
/*
id n page
----------- ---------- -----------
1 aa 5
*/
SELECT * FROM userTable WHERE name=ISNULL(@name,name) AND page=ISNULL(@page,page)
select * from usertable
where name like '%'+@name+'%' and page like '%'+@page+'%'
--只要有一个不为空它就是查询条件,如果都为空的话就相当于 select * from usertable
SELECT * FROM usertable WHERE name=ISNULL(NULLIF(@name,''),name) AND page=ISNULL(NULLIF(@page,''),page)
select * from usertable
where (name=@name and page=@page ) or (page=@page and name is null) or (name=@name and page is null) or (page is null and name is null)
select * from usertable
where (name=@name and page=@page ) or @name is null or @page is null
select * from usertable
where (name=@name and page=@page ) or name is null or page is null