Java开发网 - Re:这句sql该怎么写？(取出最近发布的n条记录)

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate

打印话题 寄给朋友 订阅主题

作者

这句sql该怎么写？(取出最近发布的n条记录)

Jason_hero

与羊共舞的狼

发贴: 43
积分: 0

于 2006-09-20 15:27

现在需要把数据表中的信息显示到页面上，
数据表里存的是会员发布的产品信息，也是广告信息，
要求是从表中取出最近发布的n条记录，
前提是一个会员只能显示一条广告信息，

麻烦各位给个解法，谢谢

{ you have been here for quite a while, I think you should know that such a SQL question should not be posted in Java基础, and a better subject line would be required.
Please be warned that I may remove (instead of redirecting) your next inappropiate post on Java基础. I'm serious. }

why edited on 2006-09-20 18:44

活不明白~~~~~~

作者	Re:这句sql该怎么写？ [Re:Jason_hero]
JiafanZhou 版主发贴: 736 积分: 61	于 2006-09-20 16:12 Use sql to retrieve the data from DB and display the message to user via servlets or JSP.. Is that a good solution? Jiafan When I was a kid I used to pray every night for a new bike. Then I realized that The Lord doesn't work that way, so I stole one and asked him to forgive me.

作者

Re:这句sql该怎么写？(取出最近发布的n条记录) [Re:Jason_hero]

why

問題兒童

总版主

发贴: 4629
积分: 388

于 2006-09-20 18:28

Which database(s)? (please do speciify when you're asking DB/SQL questions.)

Do you have the database schema (table definitions)?
Do you expect us tio guess how your 会员发布产品信息表 is like?
会员id 信息 createdDate status(i.e. active or not)

Could you use views?
Could you update the a flag in table when you insert a 信息 for a 会员?

If you want plain SQL, I'm using MS SQL syntax now and assume the field names to be userid, message, createdDate, and status


-- specify N
select top N t1.userid, t1.message, t1.createdDate
 from 产品信息表 t1
 inner join (select userid, max(createdDate) latestDate from 产品信息表 where status='A' group by userid) t2
   on t1.userid = t2.userid and t1.createdDate = t2.latestDate
order by t1.createdDate desc

已读帖子

新的帖子

被删除的帖子