双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

在多个条件查询，怎么动态生成sql语句

2013-07-24 03:51:52 1.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

party620 墨衍会员 · AI 创作全网分发关注 + 加入社区

sql语句动态拼接多个查询条件查询数据库 sql语句动态拼接多个查询条件查询数据库问题描述：公司开发中自己负责的模块业务需要根据用户选择内容去查询数据库，其中会计期间是必须的条件，默认为当前日期。用户只需要根据需要去输入就可以进行相关查询。不选择不拼接。直接上图上代码！！！后台打印sql为：拼接会计期间 : select * from jyqrsj where f_kjqj = ‘201801’ 拼接单位... 阅读详情

有3个查询条件，如果用if判断的话，得进行8次if判断，有没有什么方法，可以根据条件动态生成sql语句

...全文

计算机技术发展5大阶段,现阶段计算机技术应用及发展【摘要】现代计算机科学技术的发展和普及运用，为世界各国民众基础性生产生活实践活动的组织开展路径造成了深刻影响，本文围绕现阶段计算机技术应用及发展，择取两个具体方面，展开了简要分析。【关键词】计算机技术；应用；发展改革开放以来，计算机设备的广泛运用，促进了计算机科学技术的逐步发展成熟。计算机科学技术是具备新兴表现特征的尖端应用技术，其在现代工业生产和社会生活实践活动过程中的广泛引入运用，给世界各国基... 阅读详情

party620

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

软件高手_2016 2016-03-12

打赏
举报

引用 12 楼 hdhai9451 的回复:

[quote=引用 5 楼 lyx_1094562530 的回复:] (CraneInfo为表明；ctype,Company，RegionId为表字段)



if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end

不需要写得那么麻烦

select * 
from 
(select *,ROW_NUMBER() over (order by ccode) as num 
from CraneInfo 
where Ctype=case when @type<>'-全部-' then @type else Ctype end
	and  Company= case when @firm<>'-全部-' then @firm else Company end
	and RegionId=case when @province <>'-全部-' then  @regionId else RegionId end
)as craneInfo2 
where num between @start and @end

[/quote] 感觉这么写非常好

Andy__Huang 2013-07-24

打赏
举报

引用 5 楼 lyx_1094562530 的回复:

(CraneInfo为表明；ctype,Company，RegionId为表字段)



if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end

不需要写得那么麻烦

select * 
from 
(select *,ROW_NUMBER() over (order by ccode) as num 
from CraneInfo 
where Ctype=case when @type<>'-全部-' then @type else Ctype end
	and  Company= case when @firm<>'-全部-' then @firm else Company end
	and RegionId=case when @province <>'-全部-' then  @regionId else RegionId end
)as craneInfo2 
where num between @start and @end

csl_1022 2013-07-24

打赏
举报

declare @sWhere varchar(4000) set @sWhere = '' if (rtrim(ltrim(@No)) <> '') begin set @sWhere = @sWhere + ' and GoodsNo like ' + '''' + '%'+ @No + '%'+ '''' end if (rtrim(ltrim(@Name)) <> '') begin set @sWhere = @sWhere + ' and GoodsName like ' + '''' + '%'+ @Name + '%'+ '''' end set @sWhere='select * from table where 1=1 '+@sWhere exec(@sWhere) 给你一段例子参照这个写吧

csl_1022 2013-07-24

打赏
举报

动态拼接sql语句吧 2楼已经给出答案了

zhainanchunchun 2013-07-24

打赏
举报

很想说一句干嘛不在代码里封装查询条件呢？一定要放在数据库中吗？

Leon_He2014 2013-07-24

打赏
举报

引用 6 楼 lyx_1094562530 的回复:

[quote=引用 2 楼 stublue 的回复:] 3个条件为何是八次？楼主可以用动态sql的方式。
declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

你看下我的sql语句（在5楼），查询条件可以同时存在，而你给我的应该是只能按一个条件来[/quote]


declare @str varchar(1000)='';
declare @i1 int =0;
declare @i2 int =0;

set @str='select * from master.dbo.spt_values where 1=1'
if @i1=0
set @str=@str+' and [type]=''p'''

if @i2=0
set @str=@str+' and low >100'

--多少个条件就多少个if
Exec (@str)

party620 2013-07-24

打赏
举报

引用 2 楼 stublue 的回复:

3个条件为何是八次？楼主可以用动态sql的方式。


declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

你看下我的sql语句（在5楼），查询条件可以同时存在，而你给我的应该是只能按一个条件来

party620 2013-07-24

打赏
举报

(CraneInfo为表明；ctype,Company，RegionId为表字段)



if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end

party620 2013-07-24

打赏
举报

引用 1 楼 Ryan1221 的回复:

如果有3个查询条件，就为3个查询条件都赋个默认值，这个默认值不影响查询结果，如果查询条件存在则替换默认值

都有默认值“-全部-”，但这个默认值不在值里面

昵称90天可改一次 2013-07-24

打赏
举报

详细点发出来

Leon_He2014 2013-07-24

打赏
举报

3个条件为何是八次？楼主可以用动态sql的方式。


declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

Ryan1221 2013-07-24

打赏
举报

如果有3个查询条件，就为3个查询条件都赋个默认值，这个默认值不影响查询结果，如果查询条件存在则替换默认值

✦双11狂欢大促✦

在多个条件查询，怎么动态生成sql语句

相关推荐

双11狂欢大促