Oracle.Query中MySQL Limit的等价物包括几个表？

Question

我在 MySQL 中有以下查询，希望将其转换为 Oracle。我尝试了Oracle中的row_number()函数和子查询，但无法使其正常运行。该查询有点奇怪并且涵盖多个表。

原始 MySQL 查询：

select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,  
                uc.thumbnail,uc.filename, uc.description, uc.block_id_start, 
                uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id 
FROM
    user_clips uc, users u, user_like ul

WHERE 
     ul.user_id="+user_id+" and u.user_id=uc.user_id and  
     uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1'
ORDER by 
      virtual_clip_id DESC
LIMIT "+offset+",4"

我找不到替换 LIMIT 的正确语句，因此使查询针对 Oracle 运行。

谢谢。

Answer 1

这有点痛苦，但你可以使用 rownum

select * from
(
select rownum as row_number, base_data.* from
(
select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,
 uc.thumbnail,uc.filename, uc.description, uc.block_id_start, uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id FROM user_clips uc, users u, user_like ul

WHERE ul.user_id="+user_id+" and u.user_id=uc.user_id and
 uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1' 
) base_data
ORDER by virtual_clip_id DESC
) sorted
WHERE row_number <= X

我子选择两次的唯一原因是不同的是执行一个组，并且我将排序保留在它之外，它可以通过单个子选择来完成。

Answer 2

select * from (
select DISTINCT
    uc.virtual_clip_id
  , uc.clip_id
  , uc.duration
  , uc.title
  , uc.thumbnail
  , uc.filename
  , uc.description
  , uc.block_id_start
  , uc.block_id_end
  , u.uname
  , uc.cdate
  , uc.ctime
  , uc.privacy_level
  , uc.user_id
  , row_number() over(order by uc.virtual_clip_id DESC) row_nr
   FROM
    user_clips uc
  , users u
  , user_like ul
  WHERE
    ul.user_id         = "+user_id+"
and u.user_id          = uc.user_id
and uc.virtual_clip_id = ul.virtual_clip_id
and ul.like_status     = '1'
)
 where row_nr < 5 ;

Answer 3

select * 
from 
 (
  select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,  
                  uc.thumbnail,uc.filename, uc.description, uc.block_id_start, 
                  uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id 
                , row_number() over(order by uc.virtual_clip_id desc) rn
  FROM
      user_clips uc, users u, user_like ul
  WHERE 
       ul.user_id="+user_id+" and u.user_id=uc.user_id and  
       uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1'
 )
where rn between "+offset+" and 4