200分！！！求SQL语句求高手

ken_flash 2012-02-07 05:07:47

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尽量不要用

...全文

372 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

足球中国 2012-02-08

打赏
举报

需要用到动态sql

daswcszxw 2012-02-08

打赏
举报

路过学习。看来lz 还木有得到满意的答案？等待大佬。

挨踢直男 2012-02-08

打赏
举报

你思路不对啊
where筛选器是对每一行进行筛选的，要不使用动态sql

14楼的方案也很好

yy120925 2012-02-08

打赏
举报

如果不用if。。else。。看着很乱~而且''会出现问题~看看是不是''号周围出现问题。。帮你顶一下

777kit 2012-02-07

打赏
举报

SELECT TOP 10 *
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

qldsrx 2012-02-07

打赏
举报

declare @isEmpty int;

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)

PaulyJiang 2012-02-07

打赏
举报

sp_executesql 这个可以解决一切问题

烟波钓 2012-02-07

打赏
举报

我去~~~~ 我这是治标不治本转到Sql版去吧

declare @WebSiteId int;

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

艾米01 2012-02-07

打赏
举报

为什么不直接写一个分页的存储过程，然后直接在代码里传进相关参数就好了...

人称猪 2012-02-07

打赏
举报



Select top 10 *

from Gy_StyleClassLang with (nolock)

where  title (case @isEmpty =1 then <>'' case @isEmpty = 2 then = '' else 1=1)

ken_flash 2012-02-07

打赏
举报

[Quote=引用 8 楼 yanbuodiao 的回复:]

SQL code

declare @sqlstr nvarchar(500),
@Condition nvarchar(500)

select @sqlstr='Select top 10 *
from Gy_StyleClassLang with (nolock)
where'

select @sqlstr=@sqlstr+@Condition;--此处直接传“titl……
[/Quote]

好吧哥们，给你看我现在的结果

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

烟波钓 2012-02-07

打赏
举报



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)

EnForGrass 2012-02-07

打赏
举报

[Quote=引用 6 楼 ken_flash 的回复:]

引用 5 楼 wtnu200 的回复:

引用 4 楼 ken_flash 的回复:

我是修改他们写的代码，原SQL已经有IF ELSE了，我再嵌套IF ELSE，SQL就巨长了。。看着不爽，实在没办法就IF ELSE去

...就这原因？

原来3个条件，我再每3个里边嵌3个，那维护起来实在是巨恶心人~~
[/Quote]
那意思说switch也不能用了啊

ken_flash 2012-02-07

打赏
举报

[Quote=引用 5 楼 wtnu200 的回复:]

引用 4 楼 ken_flash 的回复:

我是修改他们写的代码，原SQL已经有IF ELSE了，我再嵌套IF ELSE，SQL就巨长了。。看着不爽，实在没办法就IF ELSE去

...就这原因？
[/Quote]

原来3个条件，我再每3个里边嵌3个，那维护起来实在是巨恶心人~~