更优雅的SQL？ [英] More elegant SQL?

问题描述

下面的查询是完美的功能，并查询单个表来查找通过连续的 userid 数字列添加的最后50个用户名。

The query below is perfectly functional, and queries a single table to find the last 50 usernames added via a sequential userid number column.

逻辑到目前为止：找出最高的userid;从中减去50;将用户名拉回更大。

Logic so far is to: find out the highest userid; subtract 50 from that; pull usernames back where greater.

但是，它看起来并不优雅，并且使用两个子查询来实现目标：

However, it doesn't look elegant, and uses two subqueries to achieve it's goal:

SELECT username  
FROM table  
WHERE userid IN  
  (SELECT userid  
   FROM table  
   WHERE userid >  
    (SELECT MAX(userid) -50  
     FROM table))

嵌套？更高效？更优雅？任何帮助将非常感谢，因为这不是最好的方法！

Is there a way to make this less nested? More efficient? More elegant? Any help would be much appreciated, as this can't be the best way!

干杯&很多感谢

Ali

Cheers & many thanks
Ali

推荐答案

提供的答案沿着正确的线。
您可以使用ROWNUM来选择TOP-N样式结果。

The answers provided are along the right lines. You can use ROWNUM to select TOP-N style results.

请注意，请注意，rownum会在预测后但在ORDER BY之前分配给查询结果。尝试类似以下内容：

Please be careful though and note that the rownum is assigned to the query results after predication but before the ORDER BY. Try something like the following:

SELECT username  
FROM 
  (SELECT username  
   FROM table  
   ORDER BY userid DESC)
WHERE rownum <= 50

这篇关于更优雅的SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！