硬化的 MySQL 限制在 Oracle.Query 中包括多个表吗?

标签: MySQL Oracle
发布时间: 2015/6/11 23:04:12
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有下面的查询在 MySQL,想要将其转换为 Oracle。我试图 row_number() 函数和子查询在 Oracle 中的,但不是能使它正常运行。查询是有点奇怪,涵盖多个表。

原始的 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"

找不到适当的语句,以取代限制所以使查询运行针对 Oracle。

谢谢你。

解决方法 1:

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
赞助商