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

2012-01-05

110818
0
SQL

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

BETWEEN數值

BETWEEN 1 AND 5

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

BETWEEN字串

比如找 S – Z 的BOOK書名

BETWEEN 'S' AND 'Z'

包含S, SQL, 到Z，但不包含Zero的書名哦!

請參考以下的範例說明


--資料準備
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) <= 'Z'
ORDER BY BOOK_NAME


--*****************************************************
--找出 出版日期為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