Question[SQL]: How can I list all book with prices greather than the average price of books of the same type?

Question: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:

 程序代码
type         title                                                                            price                
------------ -------------------------------------------------------------------------------- ---------------------
business     The Busy Executive's Database Guide                                              19.9900
…
…
…

…

 

Answer:

select a.[type], a.[title], a.[price]
    from [pubs].[dbo].[titles] a,
    (select [type], [price]=AVG([price]) from [pubs].[dbo].[titles] group by [type]) b
    where a.[type]=b.[type] and a.[price]>b.[price]

 

这里还有一个类拟的试题：

第一题比较简单，查询出销售表中，销售额大于本地区平均水平的记录，用一条sql语句就搞定了。

Sales表

OrderID	Region	Total
1	A	100.00
2	C	80.00
3	A	130.00
4	B	90.00
5	B	100.00
6	C	120.00
7	A	90.00
8	C	90.00
9	B	80.00

Sql语句：

select * from sales as s 
  inner join 
   (select avg(total) as avge,region from sales group by region) avgtable 
  on s.region = avgtable.region 
  where total > avgtable.avge