示例 INTERSECT 查询 - Amazon Redshift

示例 INTERSECT 查询

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

235494 | 23875 | 8771

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

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 列上的约束。

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)