我用分组并列显示了这样的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 Egroup by BookNameIdtbGuestBook 表的字段:id,Message(标题),BookNameId(对应另一个表的id:如健康,文物,其他),PostTime(时间)
我想每次分组并列只查询出最新的2条数据,怎么修改此SQL?
要求这样显示:
[军事] aaaaaa1 ddaadassd1
[博文] ffggghhhh3 ffggghhhh3
[健康] tttefff5 ffggghhhh5
[文物] dsfdsfdsf6 ffggghhhh6
[其他] eeeeeeee8 ddddddd8
求大牛帮帮看下,对复杂的sql有点搞不懂。
[博文] 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 Egroup by BookNameIdtbGuestBook 表的字段:id,Message(标题),BookNameId(对应另一个表的id:如健康,文物,其他),PostTime(时间)
我想每次分组并列只查询出最新的2条数据,怎么修改此SQL?
要求这样显示:
[军事] aaaaaa1 ddaadassd1
[博文] ffggghhhh3 ffggghhhh3
[健康] tttefff5 ffggghhhh5
[文物] dsfdsfdsf6 ffggghhhh6
[其他] eeeeeeee8 ddddddd8
求大牛帮帮看下,对复杂的sql有点搞不懂。
create table tbGuestBook(BookNameId varchar(8),[Message] varchar(20))
insert into tbGuestBook
select '[军事]','aaaaaa1' union all
select '[军事]','ddaadassd1' union all
select '[军事]','ffff1' union all
select '[博文]','ffggghhhh3' union all
select '[博文]','ffggghhhh3' union all
select '[博文]','dffd3' union all
select '[健康]','tttefff5' union all
select '[健康]','ffggghhhh5' union all
select '[文物]','dsfdsfdsf6' union all
select '[文物]','ffggghhhh6' union all
select '[其他]','eeeeeeee8' union all
select '[其他]','ddddddd8' union all
select '[其他]','sfdfdfsd8' union all
select '[其他]','fgghf8' union all
select '[其他]','dfgdf8'
select BookNameId,
stuff((select ','+[Message]
from tbGuestBook where BookNameId = e.BookNameId
for xml path('')),1,1,'') as BookNameList from tbGuestBook E
group 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 E
group by BookNameId/*
BookNameId BookNameList
---------- -----------------------------
[博文] ffggghhhh3,ffggghhhh3
[健康] tttefff5,ffggghhhh5
[军事] aaaaaa1,ddaadassd1
[其他] eeeeeeee8,ddddddd8
[文物] dsfdsfdsf6,ffggghhhh6
*/