Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

示例 INTERSECT 查询

将以下示例与第一个 UNION 示例进行比较。这两个示例之间的唯一差异是所使用的集合运算符,但结果完全不同。仅其中一行相同:

Copy
235494 | 23875 | 8771

这是在包含 5 行的有限结果中,同时在两个表中找到的唯一行。

Copy
select listid, sellerid, eventid from listing intersect select listid, sellerid, eventid from sales order by listid desc, sellerid, eventid limit 5; listid | sellerid | eventid --------+----------+--------- 235494 | 23875 | 8771 235482 | 1067 | 2667 235479 | 1589 | 7303 235476 | 15550 | 793 235475 | 22306 | 7848 (5 rows)

下面的查询查找 3 月份同时在纽约和洛杉矶举办的活动(已销售这些活动的门票)。这两个查询表达式之间的差异是 VENUECITY 列上的约束。

Copy
select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='Los Angeles' intersect select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='New York City' order by eventname asc; eventname ---------------------------- A Streetcar Named Desire Dirty Dancing Electra Running with Annalise Hairspray Mary Poppins November Oliver! Return To Forever Rhinoceros South Pacific The 39 Steps The Bacchae The Caucasian Chalk Circle The Country Girl Wicked Woyzeck (16 rows)