我用的是oracle,查询语句如下,主要是结果集太大,考虑到溢出问题,所以要做数据查询的分页,用sql语句实现,语句如下:
select vi.itm_id,i.itm_myid,it.itm_name,it.ITM_UNAME,i.itm_l,i.itm_w,i.itm_h,i.ITM_VOLUME,att.area_name,rownum from vendor_item vi
left join item i on i.itm_id=vi.itm_id
left join item_txt it on it.itm_id=vi.itm_id
left join area_txt att on att.area_id=i.area_id
where vi.status='A' and i.status='A' and it.status='A' and vi.ven_id=283 and rownum between 10 and 20 order by i.itm_myid
这个数据查询没有结果,但是把rownum between 10 and 20 改成rownum between 0 and 20的话就有20条记录。懂的请指教下,谢谢!
select vi.itm_id,i.itm_myid,it.itm_name,it.ITM_UNAME,i.itm_l,i.itm_w,i.itm_h,i.ITM_VOLUME,att.area_name,rownum from vendor_item vi
left join item i on i.itm_id=vi.itm_id
left join item_txt it on it.itm_id=vi.itm_id
left join area_txt att on att.area_id=i.area_id
where vi.status='A' and i.status='A' and it.status='A' and vi.ven_id=283 and rownum between 10 and 20 order by i.itm_myid
这个数据查询没有结果,但是把rownum between 10 and 20 改成rownum between 0 and 20的话就有20条记录。懂的请指教下,谢谢!
select rownum as r,t.* from(
select tableName.* from tableName order by tableColum DESC
) t where rownum<=endPos) where r>startPos
select rownum as r,t.* from(
select tableName.* from tableName order by tableColum DESC
) t where rownum<=endPos) where r>startPos
and rownum > 10 and rownum <= 20 order by i.itm_myid
1楼,2楼都是正确的写法