手机端每次分页都会 把当前页最后一条的ID 传过来 我就要根据排序 取这个ID后的几条,原来sqlserver 是弄过, mysql 没有 with as ,rownumer 。 目前是这样写:
select * from (
select @rownum := @rownum +1 AS rownum,s.cbgs_create_time,s.cbgs_id from (select @rownum:=0) t,cs_biz_goods_source s
order by s.cbgs_create_time desc
) qt
where qt.rownum >
(
select rownum from (
select @rownum := @rownum +1 AS rownum,s.cbgs_create_time,s.cbgs_id from (select @rownum:=0) t,cs_biz_goods_source s
order by s.cbgs_create_time desc
) temp where cbgs_id ='75d5e99d2f08480ead7d74f3c3990962'
)但是 qt.rownum > 子查询 这个条件查出来的结构的是空, 直接写 qt.rownum > 7 就可以查出来,我下面的子查询查出来的rownum页是7,这是因为mysql有什么机制吗
select * from (
select @rownum := @rownum +1 AS rownum,s.cbgs_create_time,s.cbgs_id from (select @rownum:=0) t,cs_biz_goods_source s
order by s.cbgs_create_time desc
) qt
where qt.rownum >
(
select rownum from (
select @rownum := @rownum +1 AS rownum,s.cbgs_create_time,s.cbgs_id from (select @rownum:=0) t,cs_biz_goods_source s
order by s.cbgs_create_time desc
) temp where cbgs_id ='75d5e99d2f08480ead7d74f3c3990962'
)但是 qt.rownum > 子查询 这个条件查出来的结构的是空, 直接写 qt.rownum > 7 就可以查出来,我下面的子查询查出来的rownum页是7,这是因为mysql有什么机制吗
select rownum from (
select @rownum := @rownum +1 AS rownum,s.cbgs_create_time,s.cbgs_id from (select @rownum:=0) t,cs_biz_goods_source s
order by s.cbgs_create_time desc
) temp where cbgs_id ='75d5e99d2f08480ead7d74f3c3990962'
)
这一大段是否可改写成:
select couont(*) from cs_biz_goods_source where cbgs_id ='75d5e99d2f08480ead7d74f3c3990962';
你不是就是想求有多少条记录吗。
***
from cs_biz_goods_source a,
(select @rownum := 0 )b
where a.cbgs_id ='75d5e99d2f08480ead7d74f3c3990962'
and a.cbgs_create_time < ifnull(a.cbgs_create_time, '2050-01-01') -- 上一页最后一个值,如果为空,给个默认值即可
order by a.cbgs_create_time desc
limit 20; -- 每页显示20条