SQL分组查询问题 求解

[liushengpiaoxu]

用到的数据表：
小说数据表：NovelName
小说章节数据表:Chapter
章节表的NovelId与小说数据表的id关联（主外键关系）

CREATE TABLE [dbo].[NovelName](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Name] [NVARCHAR](100) NULL,
)

CREATE TABLE [dbo].[Chapter](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[NovelId] [INT] NULL,
[ChapterName] [NVARCHAR](300) NULL,
)
要按照
小说名称 、 最新章节 查出一个列表数据按照小说名称分组如何实现 求解

[liushengpiaoxu]

实现效果类似这种 如图所示

[听雨停了]

按照你的表设计，应该是找Chapter表中每个NovelId对应的最大的id的名称，下面这样试试看

SELECT a.name,b.ChapterName FROM NovelName a
INNER JOIN chapter b ON a.Id=b.NovelId
INNER JOIN (
	SELECT [NovelId],MAX(id) AS maxid FROM chapter 
	GROUP BY [NovelId]
) c ON a.Id=c.NovelId AND b.Id=c.maxid

[RINK_1]

SELECT A.Name,B.ChapterName
FROM [NovelName] A
JOIN [Chapter] B ON A.Id=B.NovelId
WHERE NOT EXISTS (SELECT 1 FROM [Chapter] WHERE [NovelId]=B.[NovelId] AND [Id]>B.Id )

[吉普赛的歌]

你的意思是显示每个小说的最后一章吧：

IF OBJECT_ID('[dbo].[NovelName]') IS NOT NULL DROP TABLE [dbo].[NovelName]
IF OBJECT_ID('[dbo].[Chapter]') IS NOT NULL DROP TABLE [dbo].[Chapter]
CREATE TABLE [dbo].[NovelName](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Name] [NVARCHAR](100) NULL,
 ) 
CREATE TABLE [dbo].[Chapter](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[NovelId] [INT] NULL,
	[ChapterName] [NVARCHAR](300) NULL,
)

SELECT * FROM (
	SELECT [Name]
	,(SELECT TOP 1 [ChapterName] FROM [Chapter] AS b ORDER BY b.Id desc) AS [ChapterName]
	FROM [NovelName] AS a 
) AS t
WHERE t.[ChapterName] IS NOT NULL

[liushengpiaoxu]

谢谢大家提供帮助，明天试一下就接贴了