SQL BETWEEN 运算符
SQL BETWEEN 运算符
BETWEEN运算符选择给定范围内的值。值可以是数字，文本或日期。
BETWEEN运算符是包含的：包括开始值和结束值。
在语法之间
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
演示数据库
以下是Northwind示例数据库中“Products”表的选择：
| ProductID |
ProductName |
SupplierID |
CategoryID |
Unit |
Price |
| 1 |
Chais |
1 |
1 |
10 boxes x 20 bags |
18 |
| 2 |
Chang |
1 |
1 |
24 - 12 oz bottles |
19 |
| 3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
| 4 |
Chef Anton's Cajun Seasoning |
1 |
2 |
48 - 6 oz jars |
22 |
| 5 |
Chef Anton's Gumbo Mix |
1 |
2 |
36 boxes |
21.35 |
BETWEEN 例子
以下SQL语句选择BETWEEN 10和20之间的所有产品：
实例
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
运行实例»
NOT BETWEEN 实例
要显示上一实例范围之外的产品，请使用NOT BETWEEN：
实例
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
运行实例»
与 IN 实例之间的关系
以下SQL语句选择价格介于10和20之间的所有产品同时不要显示CategoryID为1,2或3的产品：
实例
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND NOT CategoryID IN (1,2,3);
运行实例»
BETWEEN 文本值实例
以下SQL语句选择所有具有ProductName 在 Carnarvon Tigers和Mozzarella di Giovanni 之间的产品：
实例
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
运行实例»
以下SQL语句选择所有具有ProductName在 Carnarvon Tigers和Chef Anton's Cajun Seasoning之间的产品：
实例
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
运行实例»
NOT BETWEEN 文本值范围
以下SQL语句选择所有带有ProductName 不在 Carnarvon Tigers和Mozzarella di Giovanni之间的产品：
实例
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
运行实例»
样本表
以下是Northwind示例数据库中“Orders”表的选择：
| OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
| 10248 |
90 |
5 |
7/4/1996 |
3 |
| 10249 |
81 |
6 |
7/5/1996 |
1 |
| 10250 |
34 |
4 |
7/8/1996 |
2 |
| 10251 |
84 |
3 |
7/9/1996 |
1 |
| 10252 |
76 |
4 |
7/10/1996 |
2 |
在日期之间的例子
以下SQL语句选择OrderDate在 '01 -July-1996'和'31 -July-1996'之间的所有订单：
实例
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
运行实例»
或者：
实例
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
运行实例»
