[SQL]使用BETWEEN要注意的地方

BETWEEN … AND 會選取介於2個值之間的資料。

BETWEEN數值

BETWEEN 1 AND 5

包含1, 1.01, 1.1, 4.9, 4.99到5.0

BETWEEN字串

BETWEEN 'S' AND 'Z'

比如找 S – Z 的BOOK書名
包含S, SQL, 到Z，但不包含Zero的書名哦!

範例BETWEEN (number) (string)

資料準備

DECLARE @BOOKLIST TABLE
(
ID INT IDENTITY(1,1) 
, BOOK_NAME NVARCHAR(32)
, PRICE DECIMAL(9,3)
, PUBLISH_DATE DATETIME
);
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S', 1, '2011/10/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S1', 1.01, '2011/10/10 10:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('S2', 1.111, '2011/12/10 10:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('ZERO', 3, '2011/12/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('Z', 5.001, '2011/11/10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('Z1', 5, '2011/11/10 12:10');
INSERT INTO @BOOKLIST(BOOK_NAME, PRICE, PUBLISH_DATE) VALUES('TIGER', 10, '2011/12/10 20:30');

找出單價1~5間的書 (number)

SELECT * FROM @BOOKLIST
WHERE PRICE BETWEEN 1 AND 5
ORDER BY PRICE
--單價大於5的不會找出來

找出書名S~Z的書 (string)

SELECT * FROM @BOOKLIST
WHERE BOOK_NAME BETWEEN 'S' AND 'Z'
ORDER BY BOOK_NAME
--除了Z，其他Z1, ZERO不會找出來

那如果要找S-Z的書使用 >< 吧

SELECT * FROM @BOOKLIST
WHERE LEFT(BOOK_NAME, 1) >= 'S' AND LEFT(BOOK_NAME, 1) 

範例BETWEEN (date)

找出出版日期為2011/10/10~2011/12/10的書 (date)

SELECT * FROM @BOOKLIST
WHERE PUBLISH_DATE BETWEEN '2011/10/10' AND '2011/12/10'
ORDER BY PUBLISH_DATE
--出版日期為2011/12/10 10:10, 2011/12/10 20:30沒有被找出來

找出出版日期為2011/10/10 00:00:00 ~ 2011/12/10 23:59:59 的書 (date)

SELECT * FROM @BOOKLIST
WHERE PUBLISH_DATE BETWEEN '2011/10/10 00:00:00' AND '2011/12/10 23:59:59' 
ORDER BY PUBLISH_DATE

分享來源:使用BETWEEN要注意的地方