200分!!!求SQL语句求高手
declare @isEmpty int;set @isEmpty=0;
Select top 10 *
from Gy_StyleClassLang with (nolock)
where
(case
when @isEmpty=1 then (title is not null and title <>'')
when @isEmpty=2 then (title is null or title ='')
else 1=1
end)
我的意图如上,当参数值不同时列的条件也不同,希望大牛帮助一下,能不用if else尽量不要用 --------------------编程问答-------------------- 这 SQL 不是完成版,真的没看懂需求.... --------------------编程问答-------------------- 额,一个if else搞点,为什么这么纠结 --------------------编程问答-------------------- 为何不用用if...else,根据条件,设置列字符串变量,然后和Select拼起来,或者你用sqlserver提供的一系列操作字符串的函数也可以 --------------------编程问答-------------------- 我是修改他们写的代码,原SQL已经有IF ELSE了,我再嵌套IF ELSE,SQL就巨长了。。看着不爽,实在没办法就IF ELSE去 --------------------编程问答--------------------
...就这原因? --------------------编程问答--------------------
原来3个条件,我再每3个里边嵌3个,那维护起来实在是巨恶心人~~ --------------------编程问答--------------------
那意思说switch也不能用了啊 --------------------编程问答--------------------
--------------------编程问答--------------------
declare @sqlstr nvarchar(500),
@Condition nvarchar(500)
select @sqlstr='Select top 10 *
from Gy_StyleClassLang with (nolock)
where'
select @sqlstr=@sqlstr+@Condition;--此处直接传“title is not null and title <>''”或者“title is null or title =''”--
exec(@sqlstr)
好吧哥们,给你看我现在的结果
declare @WebSiteId int;
declare @StyleClassCode varchar;
declare @StyleClassName varchar;
declare @ProQty int;
declare @IsEmpty int;
declare @Currentpage int;
declare @PageSize int;
set @WebSiteId=1;
set @StyleClassCode='';
set @Currentpage=1;
set @IsEmpty=1;
set @ProQty=1;
set @StyleClassName='';
declare @sqlcmd varchar(4000);
declare @where1 varchar(1000);
declare @where2 varchar(1000);
declare @Total int;
set @where1 =' and '+case
when @ProQty=2 then 'ProQty = 0'
when @ProQty=1 then 'ProQty > 0'
else ''
end;
set @where2=' and ('+case
when @IsEmpty=0 then ''
when @IsEmpty=1 then 'Gy_StyleClassLang.Title ='''' or Gy_StyleClassLang.Title is null'
when @IsEmpty=2 then 'Gy_StyleClassLang.Title <>'''' and Gy_StyleClassLang.Title is null'
end
+')';
set @sqlcmd='Select @Total = count(1)
from GY_StyleClass with (nolock) inner join Gy_StyleClassLang with (nolock)
on GY_StyleClass.StyleClassCode = Gy_StyleClassLang.StyleClassCode
WHERE (@WebSiteId = '''' OR @WebSiteId=0 OR WebSiteId = @WebSiteId)
AND (@StyleClassCode = '''' OR StyleClassCode = @StyleClassCode)
AND (@StyleClassName = '''' OR [StyleClassName] LIKE ''%'' + RTRIM(@StyleClassName) + ''%'')'
+@where1
+@where2
+';SELECT [StyleClassCode],[StyleClassName],[CodeLevel],[ChanelPath],[WebSiteId],[ProQty]
FROM(
SELECT ROW_NUMBER() OVER (ORDER BY StyleClassCode) AS ROWNUMBER,[StyleClassCode],[StyleClassName],[CodeLevel],[ChanelPath],[WebSiteId],[ProQty]
FROM GY_StyleClass with (nolock) inner join Gy_StyleClassLang with (nolock)
WHERE ((@WebSiteId = '''' OR @WebSiteId=0) OR WebSiteId = @WebSiteId)
AND (@StyleClassCode = '''' OR StyleClassCode = @StyleClassCode)
AND (@StyleClassName = '''' OR [StyleClassName] LIKE ''%'' + RTRIM(@StyleClassName) + ''%'')'
+@where1
+@where2
+') AS temp WHERE rownumber BETWEEN ((@Currentpage - 1) * @PageSize + 1) AND @Currentpage * @PageSize';
exec sp_executesql @sqlcmd,N'@Total int output',@Total output
--------------------编程问答--------------------
--------------------编程问答-------------------- 为什么不直接写一个分页的存储过程,然后直接在代码里传进相关参数就好了... --------------------编程问答-------------------- 我去~~~~ 我这是治标不治本 转到Sql版去吧
Select top 10 *
from Gy_StyleClassLang with (nolock)
where title (case @isEmpty =1 then <>'' case @isEmpty = 2 then = '' else 1=1)
declare @WebSiteId int;--------------------编程问答-------------------- sp_executesql 这个可以解决一切问题 --------------------编程问答--------------------
declare @StyleClassCode varchar;
declare @StyleClassName varchar;
declare @ProQty nvarchar(200);--程序代码上传 语句
declare @IsEmpty nvarchar(200);--程序代码上传 语句
declare @Currentpage int;
declare @PageSize int;
set @WebSiteId=1;
set @StyleClassCode='';
set @Currentpage=1;
set @StyleClassName='';
declare @sqlcmd varchar(4000);
declare @where1 varchar(1000);
declare @where2 varchar(1000);
declare @Total int;
set @where1 =' and '+ @ProQty
end;
set @where2=' and ('+ @IsEmpty+')';
set @sqlcmd='Select @Total = count(1)
from GY_StyleClass with (nolock) inner join Gy_StyleClassLang with (nolock)
on GY_StyleClass.StyleClassCode = Gy_StyleClassLang.StyleClassCode
WHERE (@WebSiteId = '''' OR @WebSiteId=0 OR WebSiteId = @WebSiteId)
AND (@StyleClassCode = '''' OR StyleClassCode = @StyleClassCode)
AND (@StyleClassName = '''' OR [StyleClassName] LIKE ''%'' + RTRIM(@StyleClassName) + ''%'')'
+@where1
+@where2
+';SELECT [StyleClassCode],[StyleClassName],[CodeLevel],[ChanelPath],[WebSiteId],[ProQty]
FROM(
SELECT ROW_NUMBER() OVER (ORDER BY StyleClassCode) AS ROWNUMBER,[StyleClassCode],[StyleClassName],[CodeLevel],[ChanelPath],[WebSiteId],[ProQty]
FROM GY_StyleClass with (nolock) inner join Gy_StyleClassLang with (nolock)
WHERE ((@WebSiteId = '''' OR @WebSiteId=0) OR WebSiteId = @WebSiteId)
AND (@StyleClassCode = '''' OR StyleClassCode = @StyleClassCode)
AND (@StyleClassName = '''' OR [StyleClassName] LIKE ''%'' + RTRIM(@StyleClassName) + ''%'')'
+@where1
+@where2
+') AS temp WHERE rownumber BETWEEN ((@Currentpage - 1) * @PageSize + 1) AND @Currentpage * @PageSize';
exec sp_executesql @sqlcmd,N'@Total int output',@Total output
declare @isEmpty int;--------------------编程问答-------------------- SELECT TOP 10 *
set @isEmpty=0;
Select top 10 *
from Gy_StyleClassLang with (nolock)
where (@isEmpty=1 AND ISNULL(title,'')<> '') OR
(@isEmpty=2 AND ISNULL(title,'') = '') OR @isEmpty NOT IN(1,2)
FROM Gy_StyleClassLang WITH (NOLOCK)
WHERE
CASE
WHEN @isEmpty=1 AND ISNULL(title,'') <> '' THEN 1
WHEN @isEmpty=2 AND ISNULL(title,'') = '' THEN 1
ELSE 0
END = 1 --------------------编程问答-------------------- --------------------编程问答-------------------- 如果不用if。。else。。看着很乱~而且''会出现问题~看看是不是''号周围出现问题。。帮你顶一下 --------------------编程问答-------------------- 你思路不对啊
where筛选器是对每一行进行筛选的,要不使用动态sql
14楼的方案也很好 --------------------编程问答-------------------- 路过学习。看来lz 还木有得到满意的答案?等待大佬。 --------------------编程问答-------------------- 需要用到动态sql
补充:.NET技术 , C#