SQL:如何以两种不同的顺序从同一表列中获取行，限制值为max 3值和min 3值

Question

SELECT t1.HighestFunds, t2.lowestFunds
from
   (select FundAmount as HighestFunds 
    from Convergence_Dataset
    order by FundAmount
    DESC LIMIT 3) t1,
   (select FundAmount as lowestFunds
    from Convergence_Dataset
    order by FundAmount ASC
    LIMIT 3 ) t2;

期望3 HighestFunds和3 lowestFunds来自Convergence_Dataset.FundAmount，而不是得到3行列(HighestFunds，lowestFunds)，而是得到9行。FYI，我正在使用。(数据库)

Answer 1

我想你想在这里询问一下：

(SELECT FundAmount FROM Convergence_Dataset ORDER BY FundAmount DESC LIMIT 3)
UNION ALL
(SELECT FundAmount FROM Convergence_Dataset ORDER BY FundAmount LIMIT 3)
ORDER BY FundAmount;

请注意，如果您的表有少于6项记录，则同一基金金额可以报告为最高金额和最低金额。

我们还可以使用ROW_NUMBER (或RANK)实现这一点：

WITH cte AS (
    SELECT FundAmount,
           ROW_NUMBER() OVER (ORDER BY FundAmount) rn1,
           ROW_NUMBER() OVER (ORDER BY FundAmount DESC) rn2
    FROM Convergence_Dataset
)

SELECT FundAmount
FROM cte
WHERE rn1 <= 3 OR rn2 <= 3
ORDER BY FundAmount;