Subquery 进阶

不知道什么时候用Subquery

先上题：
For each instrument, show its type, maker, the owner’s name, the corresponding orchestra name, and the number of concerts (name this column as concert_number) in which the owner played from 2013 to 2016. Take into consideration only instruments produced in 2013 or earlier.

SELECT
instruments.type,
instruments.maker,
members.name,
orchestras.name,
(SELECT COUNT (concerts.orchestra_id)
FROM concerts
WHERE concerts.year BETWEEN 2013 AND 2016 AND concerts.orchestra_id=orchestras.id) AS concert_number
FROM instruments
JOIN members
ON members.id = owner_id
JOIN orchestras
ON orchestras.id = members.orchestra_id
WHERE instruments.production_year<=2013;

为什么不用group by 来计算 number of concerts ？

we have a subquery in the SELECT clause, and we are counting number of concerts in the subquery itself. The way this subquery is running is, it is adding one value for each row(returned by the main query) and that value is same as the count of concerts performed between year 2013 and 2016 by that respective orchestra, that’s why you have c.orchestra_id=o.id.

例题：
For each orchestra, show a report consisting of:

Name of the orchestra as the name column.
Country/Countries in which the orchestra played as the country column.
Number of concerts in this country as the concerts_no column.
Average rating of the concerts played by this orchestra in this country as the avg_rating column.
Show only the rows of the report for which the orchestra played more than once in a given country.

select o.name, cc.country, cc.concerts_no, cc.avg_rating
from orchestras o
join
(select c.orchestra_id, c.country,
count(c.id) as concerts_no, avg(rating) as avg_rating
from concerts c group by c.country, c.orchestra_id
having count(c.id)>1) as cc on o.id=cc.orchestra_id
group by o.name, cc.country, cc.concerts_no, cc.avg_rating

用两个group by嵌套