sqlServer 获取 每个分类下 阅读量最高的一本书 每个分类只获取一本
如题

以下为结构

--书籍类别表

CREATE TABLE [dbo].[Category](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
)go

--所有书籍表
CREATE TABLE [dbo].[Books](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Author] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CategoryId] [int] NOT NULL,
[ReadPerson] [bigint] NOT NULL
）
go


获取每个分类下 ReadPerson最大的一本书籍的信息

在线等 高手。。。

[解决办法]

SQL code

select * from [Category]as a Cross apply(select top 1 * from  [Books] where a.ID=[CategoryId] order by [ReadPerson] desc ) as b
[解决办法]
or:
SQL code
select a.* from books a inner join(select categoryid,max(readperson) as readperson from books group by categoryidid)b on a.categoryid=b.categoryid and a.readperson=b.readperson