传入价格两个参数:最小5 最大30Table1表结构数据: name minprice maxprice A 200 300 B 3 50 C 6 100 D 29 200 得出: name minprice maxprice B 3 50 C 6 100 D 29 200 意思是说,只要minprice maxprice 之间的值包含5 30之间的值 , 那么就从Table1取出来。希望应用交集(与运算或者或运算)?
select * from Table1 where minprice >=5 and maxprice <=30
--> -->
if not object_id('Tempdb..#') is null drop table # Go Create table #([name] nvarchar(1),[minprice] int,[maxprice] int) Insert # select N'A',200,300 union all select N'B',3,50 union all select N'C',6,100 union all select N'D',29,200 Go Select * from # where 3 between [minprice] and [maxprice] or 30 between [minprice] and [maxprice] (4 個資料列受到影響) name minprice maxprice ---- ----------- ----------- B 3 50 C 6 100 D 29 200(3 個資料列受到影響)
或 Select * from # where ([minprice]<=3 and [maxprice]>=3) or ([minprice]<=30 and [maxprice]>=30)
name minprice maxprice A 200 300 B 3 50 C 6 100 D 29 200 如上,我取200 ~500,那么得出 A 200 300 D 29 200 如上,我取50~200,那么得出 name minprice maxprice A 200 300 B 3 50 C 6 100 D 29 200 就是说,范围内的值如果符合表table1,就要取出来。。
传入价格两个参数:最小5 最大30 select name, isnull(minprice,0) as minprice, isnull(maxprice,0) as maxprice from table1 where isnull(minprice,0)<=5 and isnull(maxprice,0)>=300
OO,sorry,没有别的意思,语气不太好抱歉。
declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200 --select * from @t where maxprice>=5 and minprice<=30 select * from @t where minprice between 200 and 300 or maxprice between 200 and 300 /*name minprice maxprice ---------- ----------- ----------- A 200 300 D 29 200 */
declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200 select * from @t where (maxprice >= 500 and minprice <= 500) or (minprice <= 200 and maxprice >= 200) or (minprice >= 200 and maxprice <= 500) select * from @t where (maxprice >= 200 and minprice <= 200) or (minprice <= 50 and maxprice >= 50) or (minprice >= 50 and maxprice <= 200) name minprice maxprice ---------- ----------- ----------- A 200 300 D 29 200(2 行受影响)name minprice maxprice ---------- ----------- ----------- A 200 300 B 3 50 C 6 100 D 29 200(4 行受影响)
declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200 select * from @t where (minprice between 5 and 30) or (maxprice in (5, 30))
这是我根据您的要求:无论minprice 还是maxprice 在5-30之间的就查出来,请您看一下 between 5 and 30等价于in (5, 30))
[code/=sql] select * from @t where maxprice>=5 and minprice<=30 [/code]
如果是5~1000, 正确结果的显然是: name minprice maxprice A 200 300 B 3 50 C 6 100 D 29 200 上面显然不正确。
declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200 select * from @t where (minprice between 5 and 1000) or (maxprice between 5 and 1000)
--越來越糊塗了,不知道樓主到底要什麼樣的結果 declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200 --select * from @t where maxprice>=5 and minprice<=30 select * from @t where minprice between 5 and 1000 or maxprice between 5 and 1000 /*name minprice maxprice ---------- ----------- ----------- A 200 300 B 3 50 C 6 100 D 29 200(影響 4 個資料列)*/
select * from @t where (maxprice >= 1000 and minprice <= 1000) or (minprice <= 5 and maxprice >= 5) or (minprice >= 5 and maxprice <= 1000)name minprice maxprice ---------- ----------- ----------- A 200 300 B 3 50 C 6 100 D 29 200(4 行受影响)
:),可能没有表达清晰。。谢谢各位! lihuanmei wufeng4552及所有thanks.
declare @t table(name varchar(10), minprice int,maxprice int) insert @t select 'A' ,200, 300 insert @t select 'B' , 3 , 50 insert @t select 'C' , 6 , 100 insert @t select 'D' , 29 , 200declare @dt1 datetime,@dt2 datetime select @dt1=5,@dt2=1000Select * from # where [minprice] between @dt1 and @dt2 or [maxprice] between @dt1 and @dt2 or ([minprice]<=@dt1 and [maxprice]>=@dt1) or ([minprice]<=@dt2 and [maxprice]>=@dt2)
select * from Table1 where minprice between 5 and 30 or maxprice between 5 and 30
create table #AA(UserName varchar(10), minprice int,maxprice int) insert #AA select 'A',200,300 insert #AA select 'B',3,50 insert #AA select 'C',6,100 insert #AA select 'D',29,200 --传入价格两个参数:最小5 最大30 declare @min int set @min=5 declare @max int set @max=30 select * from #AA where minprice between @min and @max or maxprice between @min and @max
例如 minprice=5; maxprice =30 select * from Table1 where (minprice>5 and minprice<30 ) or (maxprice>5 and minprice<30)也可以:select * from table1 where minprice>5 and minprice<30 union select * from table1 where maxprice>5 and minprice<30
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
select * from @t where maxprice>=5 and minprice<=30
/*name minprice maxprice
---------- ----------- -----------
B 3 50
C 6 100
D 29 200
*/
if not object_id('Tempdb..#') is null
drop table #
Go
Create table #([name] nvarchar(1),[minprice] int,[maxprice] int)
Insert #
select N'A',200,300 union all
select N'B',3,50 union all
select N'C',6,100 union all
select N'D',29,200
Go
Select
*
from
#
where
3 between [minprice] and [maxprice]
or
30 between [minprice] and [maxprice]
(4 個資料列受到影響)
name minprice maxprice
---- ----------- -----------
B 3 50
C 6 100
D 29 200(3 個資料列受到影響)
Select
*
from
#
where
([minprice]<=3 and [maxprice]>=3)
or
([minprice]<=30 and [maxprice]>=30)
A 200 300
B 3 50
C 6 100
D 29 200 如上,我取200 ~500,那么得出
A 200 300
D 29 200 如上,我取50~200,那么得出
name minprice maxprice
A 200 300
B 3 50
C 6 100
D 29 200 就是说,范围内的值如果符合表table1,就要取出来。。
select name, isnull(minprice,0) as minprice, isnull(maxprice,0) as maxprice
from table1
where isnull(minprice,0)<=5
and isnull(maxprice,0)>=300
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
--select * from @t where maxprice>=5 and minprice<=30
select * from @t where minprice between 200 and 300
or maxprice between 200 and 300
/*name minprice maxprice
---------- ----------- -----------
A 200 300
D 29 200
*/
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200 select *
from @t
where
(maxprice >= 500 and minprice <= 500)
or (minprice <= 200 and maxprice >= 200)
or (minprice >= 200 and maxprice <= 500)
select *
from @t
where
(maxprice >= 200 and minprice <= 200)
or (minprice <= 50 and maxprice >= 50)
or (minprice >= 50 and maxprice <= 200)
name minprice maxprice
---------- ----------- -----------
A 200 300
D 29 200(2 行受影响)name minprice maxprice
---------- ----------- -----------
A 200 300
B 3 50
C 6 100
D 29 200(4 行受影响)
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
select * from @t where (minprice between 5 and 30)
or (maxprice in (5, 30))
between 5 and 30等价于in (5, 30))
select * from @t where maxprice>=5 and minprice<=30
[/code]
正确结果的显然是:
name minprice maxprice
A 200 300
B 3 50
C 6 100
D 29 200
上面显然不正确。
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
select * from @t where (minprice between 5 and 1000)
or (maxprice between 5 and 1000)
declare @t table(name varchar(10), minprice int,maxprice int)
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
Select
*
from @twhere
([minprice]<=5 and [maxprice]>=5)
or
([minprice]<=1000 and [maxprice]>=1000 )
declare @t table(name varchar(10), minprice int,maxprice int)
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200
--select * from @t where maxprice>=5 and minprice<=30
select * from @t where minprice between 5 and 1000
or maxprice between 5 and 1000
/*name minprice maxprice
---------- ----------- -----------
A 200 300
B 3 50
C 6 100
D 29 200(影響 4 個資料列)*/
select *
from @t
where
(maxprice >= 1000 and minprice <= 1000)
or (minprice <= 5 and maxprice >= 5)
or (minprice >= 5 and maxprice <= 1000)name minprice maxprice
---------- ----------- -----------
A 200 300
B 3 50
C 6 100
D 29 200(4 行受影响)
lihuanmei wufeng4552及所有thanks.
insert @t select 'A' ,200, 300
insert @t select 'B' , 3 , 50
insert @t select 'C' , 6 , 100
insert @t select 'D' , 29 , 200declare @dt1 datetime,@dt2 datetime
select @dt1=5,@dt2=1000Select
*
from
#
where
[minprice] between @dt1 and @dt2
or
[maxprice] between @dt1 and @dt2
or
([minprice]<=@dt1 and [maxprice]>=@dt1)
or
([minprice]<=@dt2 and [maxprice]>=@dt2)
select * from Table1 where minprice between 5 and 30 or maxprice between 5 and 30
insert #AA select 'A',200,300
insert #AA select 'B',3,50
insert #AA select 'C',6,100
insert #AA select 'D',29,200
--传入价格两个参数:最小5 最大30
declare @min int
set @min=5
declare @max int
set @max=30
select * from #AA where minprice between @min and @max or maxprice between @min and @max
select * from Table1 where (minprice>5 and minprice<30 ) or (maxprice>5 and minprice<30)也可以:select * from table1 where minprice>5 and minprice<30
union
select * from table1 where maxprice>5 and minprice<30