BETWEEN 範圍條件 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

BETWEEN 範圍條件

BETWEEN 條件會使用關鍵字 BETWEENAND,來檢定表達式是否在一系列值的範圍內。

Syntax (語法)

expression [ NOT ] BETWEEN expression AND expression

表達式可以是數值、字元或日期時間 (datetime) 資料類型,但這些類型必須相容。範圍包含端點。

範例

第一個範例會計算有多少交易已登錄售出 2、3 或 4 張票券:

select count(*) from sales where qtysold between 2 and 4; count -------- 104021 (1 row)

範圍條件包含開頭值與結尾值。

select min(dateid), max(dateid) from sales where dateid between 1900 and 1910; min | max -----+----- 1900 | 1910

範圍條件中第一個表達式的值,必須小於第二個表達式的值。由於表達式的值,下列的範例一律會傳回 0 列:

select count(*) from sales where qtysold between 4 and 2; count ------- 0 (1 row)

不過,套用 NOT 修飾符將會反轉邏輯,產生所有列的計數:

select count(*) from sales where qtysold not between 4 and 2; count -------- 172456 (1 row)

下列的查詢會傳回擁有 20,000 到 50,000 個座位的場地清單:

select venueid, venuename, venueseats from venue where venueseats between 20000 and 50000 order by venueseats desc; venueid | venuename | venueseats ---------+-------------------------------+------------ 116 | Busch Stadium | 49660 106 | Rangers BallPark in Arlington | 49115 96 | Oriole Park at Camden Yards | 48876 ... (22 rows)