BETWEEN 範圍條件 - AWS Clean Rooms

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

BETWEEN 範圍條件

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

語法

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)

下面的例子演示了使用 BETWEEN 的日期值:

select salesid, qtysold, pricepaid, commission, saletime from sales where eventid between 1000 and 2000 and saletime between '2008-01-01' and '2008-01-03' order by saletime asc; salesid | qtysold | pricepaid | commission | saletime --------+---------+-----------+------------+--------------- 65082 | 4 | 472 | 70.8 | 1/1/2008 06:06 110917 | 1 | 337 | 50.55 | 1/1/2008 07:05 112103 | 1 | 241 | 36.15 | 1/2/2008 03:15 137882 | 3 | 1473 | 220.95 | 1/2/2008 05:18 40331 | 2 | 58 | 8.7 | 1/2/2008 05:57 110918 | 3 | 1011 | 151.65 | 1/2/2008 07:17 96274 | 1 | 104 | 15.6 | 1/2/2008 07:18 150499 | 3 | 135 | 20.25 | 1/2/2008 07:20 68413 | 2 | 158 | 23.7 | 1/2/2008 08:12

請注意,儘管 BETWEEN 的範圍包括在內,但日期默認為具有 00:00:00 的時間值。範例查詢的唯一有效 1 月 3 日資料列是銷售時間為的資料列1/3/2008 00:00:00