英语中between and表示在什么之间,数据库也不例外啊,你都没有and,当然是空的大于3000:>3000 小于3000:<3000 2000到3000之间: between 2000 and 3000MSSQL的这样写就包括了2000和3000这两个值在内。MYSQL的不清楚
但是 between 3000 and 3500 也是查找不到啊.是不是某一范围内的数值不应该这样保存:2000-3000,那么要怎样保存呢?如果保存为3000这样可以查找出来,但是我想保存的值是在某一范围内,如:2000-3000 那么要怎样保存这个值。
用字符串存入,查询的时候把它截取出来再用between and
/* id salary ----------------- 1 2000-3000 ---------------- 2 4000-5000 */--生成测试数据: go if OBJECT_ID('tbl') is not null drop table tbl go create table tbl( id int, salary varchar(10) ) go insert tbl select 1,'2001-3000' union all select 2,'3001-4000' union all select 3,'4001-5000' union all select 4,'5001-6000' union all select 5,'6001-7000'select *from tblselect *from tbl where cast(LEFT(salary,CHARINDEX('-',salary)-1) as int) between 2000 and 5000 and cast(right(salary,CHARINDEX('-',salary)-1) as int) between 2000 and 5000/* id salary 1 2001-3000 2 3001-4000 3 4001-5000 */MSSQL中可以这样实现,MYSQL应该也是这样
小于3000:<3000
2000到3000之间: between 2000 and 3000MSSQL的这样写就包括了2000和3000这两个值在内。MYSQL的不清楚
id salary
-----------------
1 2000-3000
----------------
2 4000-5000
*/--生成测试数据:
go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id int,
salary varchar(10)
)
go
insert tbl
select 1,'2001-3000' union all
select 2,'3001-4000' union all
select 3,'4001-5000' union all
select 4,'5001-6000' union all
select 5,'6001-7000'select *from tblselect *from tbl where cast(LEFT(salary,CHARINDEX('-',salary)-1) as int) between 2000 and 5000
and cast(right(salary,CHARINDEX('-',salary)-1) as int) between 2000 and 5000/*
id salary
1 2001-3000
2 3001-4000
3 4001-5000
*/MSSQL中可以这样实现,MYSQL应该也是这样
http://zhidao.baidu.com/question/323155827.html
FROM `work`
WHERE substring_index(`salary` ,'-',1)+0 <=3000
and substring_index(`salary` ,'-',-1)+0 >=3000
-----------------
1 2000-3000
----------------
2 4000-5000+======================
salary 这个字段的类型是字符串吧,用 between 这个比较合适吗?你保存的就是一个范围,这不是自己找麻烦吗
(id ,min_num ,max_num)
容易处理一点