sql-server - 在SQL中获取最近的较高和较低的行

Question

我正在尝试选择高于 2000 的前 5 个金额和低于 2000 的前 5 个金额，并在单个结果集中返回数据。

SELECT TOP 5 * 
FROM   bid 
WHERE  amount >= 2000 
ORDER  BY amount ASC 
UNION
SELECT TOP 5 * 
FROM   bid 
WHERE  amount < 2000 
ORDER  BY amount DESC 

看起来这不是 union 的使用方式，因为我遇到了运行时错误。

Incorrect syntax near the keyword 'union'.

编写所需查询的正确方法是什么？

Answer 1

SELECT * 
FROM   (SELECT TOP 5 * 
        FROM   bid 
        WHERE  amount >= 2000 
        ORDER  BY amount ASC) t1 
UNION 
SELECT * 
FROM   (SELECT TOP 5 * 
        FROM   bid 
        WHERE  amount < 2000 
        ORDER  BY amount DESC) t2 

您可以为结果添加额外的顺序:

SELECT * 
FROM   (SELECT * 
        FROM   (SELECT TOP 5 * 
                FROM   bid 
                WHERE  amount >= 2000 
                ORDER  BY amount ASC) t1 
        UNION 
        SELECT * 
        FROM   (SELECT TOP 5 * 
                FROM   bid 
                WHERE  amount < 2000 
                ORDER  BY amount DESC) t2) t3 
ORDER  BY t3.amount