SQL 查询问题

[已解决问题] 解决于 2012-11-15 15:51

题目：一表里有作者、书的类别、销售数量。要求查询出每个作者的5种畅销书（一个作者可以写多种书）怎样实现？

SQL Server 数据库

程海 | 初学一级 | 园豆：11
提问于：2012-11-14 18:30

< >

最佳答案

--创建测试数据
create table books
(
    Name nvarchar(50),
    Author nvarchar(50),
    SaleCount int,
)

insert into books values('C#一点通', '刘君浩', 320)
insert into books values('21天精通C#', '刘君浩', 220)
insert into books values('设计模式C#版', '刘君浩', 120)
insert into books values('SQL基础', '刘君浩', 420)
insert into books values('.net框架', '刘君浩', 60)
insert into books values('SharePoint应用', '刘君浩', 520)
insert into books values('SharePoint开发技术', '刘君浩', 620)
insert into books values('VB.NET设计模式', '萧风', 600)
insert into books values('精通VB.NET', '萧风', 380)
insert into books values('21天精通VB.NET', '萧风', 380)
insert into books values('VB.NET入门经典', '萧风', 580)
insert into books values('VB.NET高级编程', '萧风', 320)
insert into books values('SharePoint入门基础', '萧风', 220)
insert into books values('企业架构', '萧风', 120)

--查询语句
select Author, Name from
(
select a.Author, a.Name, SaleCount, ROW_NUMBER() over (partition by a.Author order by a.Author, a.SaleCount desc) n
from (select Author, Name, sum(SaleCount) SaleCount from books group by Author, Name) a
)
b where n<=5 order by Author, SaleCount desc

奖励园豆：5

月飘冥 | 菜鸟二级 |园豆：207 | 2012-11-14 20:29

select Author, Name from
(
select Author, Name, SaleCount, ROW_NUMBER() over (partition by Author order by Author, SaleCount desc) n
from books
)
a where n<=5 order by Author, SaleCount desc

月飘冥 | 园豆：207 (菜鸟二级) | 2012-11-14 20:35

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

SQL 查询问题

欢迎，请先登录或者注册。