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

联接示例

以下查询是一个外部联接。当在其他表中找不到匹配项时,左外部联接和右外部联接保留某个已联接表中的值。左表和右表是语法中列出的第一个表和第二个表。NULL 值用于填补结果集中的“空白”。

此查询匹配 LISTING(左表)和 SALES(右表)中的 LISTID 列值。结果表明列表 2、3 和 5 不会生成任何销售值。

Copy
select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm --------+--------+-------- 1 | 728.00 | 109.20 2 | | 3 | | 4 | 76.00 | 11.40 5 | 525.00 | 78.75 (5 rows)

以下查询是 FROM 子句中的两个子查询的内部联接。此查询查找不同类别的活动 (音乐会和演出) 的已售门票数和未售门票数:

Copy
select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold -----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 (2 rows)

这些 FROM 子句子查询是 子查询;它们可返回多个列和行。