有两张表bookTable commentTable
id(identity 1,1,primaty key) bookname(varchar(50)
1 xml
2 c++
3 sql
4 oracal
b:
id(identity 1,1,primary key) bookId(a的外键) userId content(text) commenttime(DateTime)
1 1 d dfgjkkghh 2006-9-29
2 1 d jkkl;gkl; 2006-9-29
3 2 a dfdfdhghdk 2006-9-28
4 2 a 65676986986 2006-5-9
5 3 ggghghgh 2006-9-10
6 4 ghjkjkl 2006-9-25
7 1 rsggah 2006-9-16
现在要选出根据时间降序排列，排名前三的无重复的记录，其中要包括bookid name content time 的信息
这个SQL语句如何写啊？
xml c# 8 333333 2006-09-27 admin
asp.net 4 NULL NULL NULL
asp.net 2.0 5 NULL NULL NULL
select rowid = identity(int,1,1),a.bookname,a.id,b.userId,b.title,b.content,b.commenttime into #3
from bookTable as a
right join commentTable as b on a.id=b.bookId
order by b.commenttime desc
select TOP 3 bookname,id,content,commenttime,userId
from #3 d
where not exists(select 1 from #3 where rowid < d.rowid and d.userId=userid)
drop table #3
select 1 指选出第一笔数据
当 join后选出来的数据有空的null时，如果不要空的fight join 改成left join
这里的select 1 指的是选出count(*)行，每一行都是1，针对的是identity类型
--------------------------------
alter table tablename add id int identity(1,1)
go
delect from tablename where id not in(select min(id) from tablename group by no,name)
go
alter table tablename drop column id
select rowid = identity(int,1,1),a.name,a.bookid,b.content,b.time into #
from a as a
left join b as b on a.bookid=b.bookId
order by b.time desc
select TOP 3 name,bookid,content,time
from # d
where not exists(select 1 from # where d.name = name and rowid < d.rowid )
drop table #
