全部产品
Search
文档中心

云原生数据仓库AnalyticDB:子查询

更新时间:May 22, 2024

以下示例查询门票销量排名前10的卖家,WHERE子句中包含一个表子查询,子查询生成多个行,每行包含一列数据。

说明

表子查询可以包含多个列和行。

select firstname, lastname, cityname, max(qtysold) as maxsold
from users join sales on users.userid=sales.sellerid
where users.cityname not in(select venuecity from venue)
group by firstname, lastname, cityname
order by maxsold desc, cityname desc
limit 10;

firstname  | lastname |      cityname  | maxsold
-----------+----------+----------------+---------
Noah       | Guerrero | Worcester      |       8
Isadora    | Moss     | Winooski       |       8
Kieran     | Harrison | Westminster    |       8
Heidi      | Davis    | Warwick        |       8
Sara       | Anthony  | Waco           |       8
Bree       | Buck     | Valdez         |       8
Evangeline | Sampson  | Trenton        |       8
Kendall    | Keith    | Stillwater     |       8
Bertha     | Bishop   | Stevens Point  |       8
Patricia   | Anderson | South Portland |       8