我在前台显示五条数据,是根据时间倒叙,如果同一个酒店出现两次,就根据时间去显示最后一个时间,我的sql语句如下,
SELECT * FROM cm_offer WHERE sys_deleted = 'N' AND off_status='Published' AND off_newonhome='Y'
group by off_hotel order by sys_modifiedon Desc LIMIT 5其中sys_modifiedon是存时间的字段。但是显示遇到一个问题,就是有最近的三条记录1,2,3,都是属于同一个酒店,但是前台应该只显示3这个酒店,因为是按时间来排除语句,但是,前台就是显示1这条记录,我找了半天原因,斗没有找到,大家帮帮忙,看看,是不是哪里出错了,应该怎么修改?谢谢。。
SELECT * FROM cm_offer WHERE sys_deleted = 'N' AND off_status='Published' AND off_newonhome='Y'
group by off_hotel order by sys_modifiedon Desc LIMIT 5其中sys_modifiedon是存时间的字段。但是显示遇到一个问题,就是有最近的三条记录1,2,3,都是属于同一个酒店,但是前台应该只显示3这个酒店,因为是按时间来排除语句,但是,前台就是显示1这条记录,我找了半天原因,斗没有找到,大家帮帮忙,看看,是不是哪里出错了,应该怎么修改?谢谢。。
既然是同一个酒店,你用的是:group by off_hotel 当然显示一条了
select top 5,* from cm_offer group by off_hotel order by sys_modifiedon Desc
看看这样是得到多少条数据
改为如下:
SELECT off_hotel,Max(sys_modifiedon) as mytime FROM cm_offer WHERE sys_deleted = 'N' AND off_status='Published' AND off_newonhome='Y'
group by off_hotel order by mytime Desc LIMIT 5
(
SELECT off_hotel,Max(sys_modifiedon) as mytime FROM cm_offer WHERE sys_deleted = 'N' AND off_status='Published' AND off_newonhome='Y'
group by off_hotel
) as B
on A.off_hotel=B.off_hotel and A.sys_modifiedon=B.mytime
order by mytime Desc LIMIT 5
改成
order by sys_modifiedon Desc LIMIT 5