select * from t_group where group_id=(case @group_id when -1 then -1=-1 else @group_id end) and obj_type = (case @obj_type when -1 then -1=-1 else @obj_type end) and group_name =(case @group_name when -1 then -1=-1 else @group_name end)
select * from t_group where (group_id = @group_id or @group_id is null) and (obj_type = @obj_type or @obj_type is null) and (group_name = @group_name or @group_name is null) ========================== 这个怎么个不中法?
select * from t_group where group_id = @group_id or obj_type = @obj_type or group_name = @group_name
select * from t_group where group_id = isnull(@group_id,groupid) and obj_type = isnull(@obj_type,obj_type) and group_name = isnull(@group_name,group_name)
这个是我在别的地方看到的,也许是我不懂吧。 请问:这样写:怎样调用? procedure_name query_t_group exec query_t_group '','','' 这样就是查询所有的吗? 那个 @group_id is null 是怎么表现的。 select * from t_group where @group_id is null and @obj_type is null and @group_name is null 什么数据也没有,显然不符合我的需要啊!
用 or 不妥啊!我 传入 group_id 明确定位到 一条数据,用 or 的话,就把所有满足条件的都查出来了。
select * from t_group where group_id=(case @group_id when -1 then group_id else @group_id end) and obj_type = (case @obj_type when -1 then obj_type else @obj_type end) and group_name =(case @group_name when -1 then group_name else @group_name end) 修改下!
--这个样子 用3个isnull select * from t_group where group_id = isnull(@group_id,groupid) and obj_type = isnull(@obj_type,obj_type) and group_name = isnull(@group_name,group_name)
--1 select * from t_group where group_id=(case when @group_id is null then group_id else @group_id end) and obj_type = (case when @obj_type is null then obj_type else @obj_type end) and group_name=(case when @group_name is null then group_name else @group_name end) --2 select * from t_group where group_id = isnull(@group_id,groupid) and obj_type = isnull(@obj_type,obj_type) and group_name = isnull(@group_name,group_name)
where (group_id = @group_id or @group_id is null)
and (obj_type = @obj_type or @obj_type is null)
and (group_name = @group_name or @group_name is null)
==========================
这个怎么个不中法?
where group_id = @group_id or obj_type = @obj_type or group_name = @group_name
*
from
t_group
where
group_id = isnull(@group_id,groupid)
and
obj_type = isnull(@obj_type,obj_type)
and
group_name = isnull(@group_name,group_name)
这个是我在别的地方看到的,也许是我不懂吧。
请问:这样写:怎样调用?
procedure_name query_t_group exec query_t_group '','','' 这样就是查询所有的吗? 那个 @group_id is null 是怎么表现的。
select * from t_group
where @group_id is null
and @obj_type is null
and @group_name is null
什么数据也没有,显然不符合我的需要啊!
用 or 不妥啊!我 传入 group_id 明确定位到 一条数据,用 or 的话,就把所有满足条件的都查出来了。
select * from t_group
where
group_id=(case @group_id when -1 then group_id else @group_id end)
and
obj_type = (case @obj_type when -1 then obj_type else @obj_type end)
and
group_name =(case @group_name when -1 then group_name else @group_name end) 修改下!
select * from t_group where
group_id = isnull(@group_id,groupid) and
obj_type = isnull(@obj_type,obj_type) and
group_name = isnull(@group_name,group_name)
select * from t_group
where group_id=(case when @group_id is null then group_id else @group_id end)
and obj_type = (case when @obj_type is null then obj_type else @obj_type end)
and group_name=(case when @group_name is null then group_name else @group_name end)
--2
select * from t_group
where group_id = isnull(@group_id,groupid)
and obj_type = isnull(@obj_type,obj_type)
and group_name = isnull(@group_name,group_name)
语法就是 9 楼的写法了。
顶多,加点 like '%'+@group_name+'%' 之类的。谢谢诸位的解答!
不过,很怀恋 JAVA 的 三元表达式 ? : 啊!