[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的不會找出來
