取出分组后的前N条数据,笔记记录。

sql2005及其以后可以用,使用cte来,

WITH ct
     AS (SELECT at.peoplemark,
                at.TraceTitle,
                at.traceid,
                at.PostDate
         FROM   cms_article_traceback at,
                cms_people_extension p
         WHERE  p.articleid = at.PeopleId
                AND p.classid = 438)
SELECT TOP 20 peoplemark,
              TraceTitle,
              traceid,
              PostDate
FROM   (SELECT peoplemark,
               TraceTitle,
               traceid,
               PostDate,
               Row_number()
                 OVER (
                   partition BY traceid
                   ORDER BY postdate DESC) rn
        FROM   ct) b
WHERE  b.rn = 1
ORDER  BY b.postdate DESC 

如果在sql2000,那


select * from tb t where not exists(select 1 from where userid=t.userid and [time]>t.[time])

posted on 2012-05-22 16:12  划风  阅读(189)  评论(0编辑  收藏  举报

导航