group by用法
我用分组并列显示了这样的sql

[军事] aaaaaa1 ddaadassd1 ffff1
[博文] ffggghhhh3 ffggghhhh3 dffd3
[健康] tttefff5 ffggghhhh5
[文物] dsfdsfdsf6 ffggghhhh6
[其他] eeeeeeee8 ddddddd8 sfdfdfsd8 fgghf8 dfgdf8

这是我的sql：

select BookNameId,stuff((select ','+[Message]

from tbGuestBook where BookNameId = e.BookNameId

for xml path('')),1,1,'') as BookNameList from tbGuestBook E

group by BookNameId



tbGuestBook 表的字段：id，Message（标题），BookNameId（对应另一个表的id：如健康，文物，其他），PostTime(时间)


我想每次分组并列只查询出最新的2条数据，怎么修改此SQL？


要求这样显示：


[军事] aaaaaa1 ddaadassd1
[博文] ffggghhhh3 ffggghhhh3
[健康] tttefff5 ffggghhhh5
[文物] dsfdsfdsf6 ffggghhhh6
[其他] eeeeeeee8 ddddddd8


求大牛帮帮看下，对复杂的sql有点搞不懂。




[解决办法]

SQL code

create table tbGuestBook(BookNameId varchar(8),[Message] varchar(20))insert into tbGuestBookselect '[军事]','aaaaaa1' union allselect '[军事]','ddaadassd1' union allselect '[军事]','ffff1' union allselect '[博文]','ffggghhhh3' union allselect '[博文]','ffggghhhh3' union allselect '[博文]','dffd3' union allselect '[健康]','tttefff5' union allselect '[健康]','ffggghhhh5' union allselect '[文物]','dsfdsfdsf6' union allselect '[文物]','ffggghhhh6' union allselect '[其他]','eeeeeeee8' union allselect '[其他]','ddddddd8' union allselect '[其他]','sfdfdfsd8' union allselect '[其他]','fgghf8' union allselect '[其他]','dfgdf8' select BookNameId,stuff((select ','+[Message] from tbGuestBook where BookNameId = e.BookNameId for xml path('')),1,1,'') as BookNameList from tbGuestBook Egroup by BookNameId/*BookNameId BookNameList---------- ---------------------------------------------[博文]       ffggghhhh3,ffggghhhh3,dffd3[健康]       tttefff5,ffggghhhh5[军事]       aaaaaa1,ddaadassd1,ffff1[其他]       eeeeeeee8,ddddddd8,sfdfdfsd8,fgghf8,dfgdf8[文物]       dsfdsfdsf6,ffggghhhh6*/--加上top 2 不就ok了吗？select BookNameId,stuff((select top 2 ','+[Message] from tbGuestBook where BookNameId = e.BookNameId for xml path('')),1,1,'') as BookNameList from tbGuestBook Egroup by BookNameId/*BookNameId BookNameList---------- -----------------------------[博文]       ffggghhhh3,ffggghhhh3[健康]       tttefff5,ffggghhhh5[军事]       aaaaaa1,ddaadassd1[其他]       eeeeeeee8,ddddddd8[文物]       dsfdsfdsf6,ffggghhhh6*/