27,579
社区成员
发帖
与我相关
我的任务
分享
declare @userid int
set @userid=123
select a.*
from userbaseinfo a inner join condition b on a.id=b.userid and a.isbuy=b.isbuy and
a.price between
convert(int,left(b.pricerange,charindex('~',b.pricerange)-1)) and
convert(int,right(b.pricerange,len(b.pricerange)-charindex('~',b.pricerange)))
where a.id=@userid
declare @userid int
set @userid=123
select a.*
from userbaseinfo a inner join condition b on a.id=b.userid and a.isbuy=b.isbuy and
a.price between
convert(int,left(b.pricerange,charindex('~',b.pricerange)-1)) and
convert(int,right(b.pricerange,len(b.pricerange)-charindex('~',b.pricerange)))
declare @userid int,@isbuy bit,@a int,@b int
set @userid=123
set @isbuy=0
set @a=2000
set @b=3000
select a.*
from userbaseinfo a inner join condition b on a.id=b.userid
where a.id=@userid and a.isbuy=@isbuy and
convert(int,left(b.pricerange,charindex('~',b.pricerange)-1))=@a and
convert(int,right(b.pricerange,len(b.pricerange)-charindex('~',b.pricerange)))=@b
create table condition(ID int,isbuy bit,pricerange varchar(20),userid int)
insert into condition select 2, 0, '2000~3000', 123
insert into condition select 3, 1, '3000~5000', 125
go
select convert(int,left(pricerange,charindex('~',pricerange)-1))a,convert(int,right(pricerange,len(pricerange)-charindex('~',pricerange)))b from condition
/*
a b
----------- -----------
2000 3000
3000 5000
(2 行受影响)
*/
go
drop table condition
select a.*
from userbaseinfo a inner join condition b on a.id=b.userid
where a.id=@userid and b.pricerange='2000~3000'
select
a.*
from
userbaseinfo a,condition b
where
a.id=b.userid
and
b.pricerange='2000~3000'
and
a. isbuy <> 0
and
b.userid=@userid
select
a.*
from
userbaseinfo a,condition b
where
a.id=b.userid
and
b.pricerange='2000~3000'
and
b.userid=@userid
create proc procName
@userid int
as
begin
select a.* from 主表 a, 次表 b
where a.id = b.id and a. isbuy <> 0 and
b.pricerange = '2000-3000' and b.userid = @userid
end
create proc procName
@id int
as
begin
select a.* from 主表 a, 次表 b
where a.id = b.id and a. isbuy <> 0 and
b.pricerange = '2000-3000' and a.id = @id
end
select a.* from 主表 a, 次表 b where a.id = b.id and a. isbuy <> 0 and and b.pricerange = '2000-3000'