Question[SQL]: How can I list all book with prices greather than the average price of books of the s...-优快云博客

本文提供了一个SQL查询案例，展示了如何从数据库中筛选出价格高于同类平均价格的商品，以及销售额超过所在地区平均水平的销售记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

转载于:https://www.cnblogs.com/chenjunbiao/archive/2008/10/14/1760183.html