mssql between应用举例


一、mssql between关键字功能简介

between关键字需要和and关键字一起搭配使用,并叠加到where后面,组合成表格的查询条件。
常见语法格式如下:

1.1
select [列名1],[列名2],[列名3],[列名4] … from [表名] where [列名] between [数值1] and [数值2]
1.2
select [列名1],[列名2],[列名3],[列名4] … from [表名] where [列名] not between [数值1] and [数值2]
注意事项:
通常 数值1 比 数值2 小
如果”数值1″、“数值2” 为非数值型和日期型,那么这些类型会先转换为ascii码,然后进行比较。


二、mssql between具体用法,边界说明

create table A(keyId int identity,writeDate datetime,info1 varchar(20))
go
insert into  A (writeDate,info1)values('2017-8-1','a')
insert into  A (writeDate,info1)values('2017-8-2','b')
insert into  A (writeDate,info1)values('2017-8-3','c')
insert into  A (writeDate,info1)values('2017-8-4','d')
insert into  A (writeDate,info1)values('2017-8-5','e')
insert into  A (writeDate,info1)values('2017-8-6','f')

select keyId,writedate,info1 from A where keyId between 2 and 4 
select keyId,writedate,info1 from A where keyId not between 2 and 4 
select keyId,writedate,info1 from A where writeDate between '2017-8-3' and '2017-8-5' 

truncate table A 
drop table A 



between 边界注意事项:
where keyId between 2 and 4 的功能为:
将keyId 大于等于2 并且小于等于4的行数据展示出来
where keyId not between 2 and 4 的功能为:
将keyId 不大于等于2 并且 不小于等于4的行数据展示出来

相关阅读:
sql where 关键字简介
sql between 关键字简介