在SQL中排除前n行和后n行 [英] Exclude top and bottom n rows in SQL

问题描述

我正在尝试查询数据库，但是从表中排除了第一行和最后一行.这是一个示例表:

I'm trying to query a database but excluding the first and last rows from the table. Here's a sample table:

id | val
--------
 1    1
 2    9
 3    3
 4    1
 5    2
 6    6
 7    4

在上面的示例中，我想先按val对其进行排序，然后排除查询的第一行和最后一行.

In the above example, I'd first like to order it by val and then exclude the first and last rows for the query.

id | val
--------
 4    1
 5    2
 3    3
 7    4
 6    6

这是我想要的结果集.注意第1行和第2行被排除，因为它们分别具有最低和最高的val.

This is the resulting set I would like. Note row 1 and 2 were excluded as they had the lowest and highest val respectively.

我已经考虑了LIMIT，TOP和其他一些问题，但无法获得理想的结果.如果有一种方法可以做到这一点(使用first/last％甚至比first/last n更好)，我想不通.

I've considered LIMIT, TOP, and a couple of other things but can't get my desired result. If there's a method to do it (even better with first/last % rather than first/last n), I can't figure it out.

推荐答案

您可以尝试使用此伴侣:

You can try this mate:

SELECT * FROM numbers
WHERE id NOT IN (
    SELECT id FROM numbers
    WHERE val IN (
        SELECT MAX(val) FROM numbers
    ) OR val IN (
        SELECT MIN(val) FROM numbers
    )
);

这篇关于在SQL中排除前n行和后n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！