现有一个知识表(knowledge ),项目要求主页显示十条知识,显示规则为:前三条按浏览量排序,后七条按照发布时间排序。
解决方案:
建立一个视图(top_knowledge),语句如下:
create or replace view top_knowledge as
select "ID","TITLE","USERID","PUBLISHTIME","TYPE","CONTENT","RETRANSMISSIONS","LIKES","STATUS","PAGEVIEW"
from (SELECT b.*, DECODE(rownum, 1, 1, 2, 2, 3, 3, 4) rank
FROM (SELECT a.*
FROM KNOWLEDGE a
order by a.PAGEVIEW desc, PUBLISHTIME desc) b) c
order by c.rank, c.PUBLISHTIME desc;
注:pageview为浏览量 ,publishtime为知识发布时间
后台利用hibernate查询此视图 ,获取前十条 记录。
前端页面利用<c:if>标签进行判断显示,代码如下:
<c:if test="${loop.count<4}">
<img alt="" src="${pageContext.request.contextPath}/images/hot.jpg" width="15px" height="15px">${knowledge.id.title}
<c:if test="${loop.count==3}"></c:if>
</c:if>
<c:if test="${loop.count>3}"><img alt="" src="${pageContext.request.contextPath}/images/new.png" width="15px" height="15px">${knowledge.id.title}</c:if>
显示效果如下图: