表:
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)