很简单的要求,但试了好几种方法,最后才写出来

      表:  

           id news_class news_name news_text news_date user_name click
            5    a                   a                    a          2008-01-01    a              1
            6    b                   b                    b         2008-02-02    b              2
            7    c                    c                    c         2008-03-03    c               3
             8   d                   d                    d         2008-04-05    b               4
           10   d                   d                     ff       2008-05-05     a              3
           11   a                   d                     fdfd   2008-05-05     b               4

 

求出每个user_name的最新发布的消息的内容

 

select id,news_class,news_name,news_text,uns.news_date,uns.user_name,click from user_news_site uns
inner join (select user_name,max(news_date) news_date from user_news_site group by user_name) un
on uns.user_name = un.user_name and uns.news_date = un.news_date

 

本来想用news_date order by desc,再取出distinct user_name,没实现…… 

 

呵呵,得到帮助

SELECT * FROM user_news_site A
WHERE news_date=(SELECT TOP 1 news_date FROM user_news_site  WHERE A.[user_name]=[user_name] ORDER BY news_date DESC)