我是SQL新手,我想在不使用LIMIT的情况下获取第5个最高值。下面是我正在尝试的代码,但它不能正常工作。它显示的是第5个最低值而不是第5个最高值。
SELECT a . * FROM user AS a WHERE 5 = (SELECT count( DISTINCT b.id ) FROM user AS b WHERE b.id >= a.id ORDER BY a.id DESC)
有人能帮我吗?
gzjq41n41#
您还可以执行以下操作:
SET @nth := 5; SELECT a.* FROM jos_modules AS a WHERE @nth = ( SELECT COUNT(b.id) FROM user AS b WHERE a.id >= b.id );
n8ghc7c12#
试试这个
SELECT a . * FROM user AS a WHERE 5 = ( SELECT count( DISTINCT b.id ) FROM user AS b WHERE a.id >= b.id ORDER BY a.id )
q1qsirdb3#
select * from ( select a.* , row_number() over (order by id asc) as RANK from a ) where RANK=5 ;
如果您使用的是Teradata DB,则可以使用qualify语句:
select * from a qualify row_number () over(order by id asc)=5;
xqkwcwgp4#
选择 * from(选择一个.*,row_number()over(order by id asc)作为来自工作进程的RANK)AS A,其中RANK=5 ;
4条答案
按热度按时间gzjq41n41#
您还可以执行以下操作:
n8ghc7c12#
试试这个
q1qsirdb3#
如果您使用的是Teradata DB,则可以使用qualify语句:
xqkwcwgp4#
选择 * from(选择一个.*,row_number()over(order by id asc)作为来自工作进程的RANK)AS A,其中RANK=5 ;