MySQL select不等于子查询中的限制

Question

MySQL #1235 -此版本的MySQL尚不支持'LIMIT &IN/ALL/ANY/SOUN子查询‘

给定1个表，如下所示

Item | Name       | Price
----- ------------ --------
1    | Adidas     | 310.00
2    | Nike Run   |  30.00
3    | Puma       | 150.00
4    | Nike Women |  20.00
5    | NB         |  20.00

希望选择记录并返回金额。不要把两个最高价格的记录相加。

SELECT SUM(Price) as total_amount
FROM `test`
WHERE Item NOT IN (
    SELECT Price 
    FROM `test`
    ORDER BY Price DESC
    LIMIT 2)

预期结果：

total_amount
------------
   70.00

如何在子查询中使用JOIN或替代限制？

谢谢。

Answer 1

以下是使用子查询和limit / offset的一种选择

select sum(price)
from (
  select *
  from test
  order by price desc
  limit 999999999
  offset 2
  ) t

• SQL Fiddle Demo

只需确保限制值大于潜在行数(显然是18446744073709551615)……

或者，您可以使用user-defined variables

select sum(price)
from (
  select *, @rn:=@rn + 1 rn
  from test cross join (select @rn:= 0) t
  ) t
where rn > 2

如果您希望排除可能超过2个记录的2个最高价格，这也适用于user defined variables

select sum(price)
from (
  select *, @rn:=if(@prevPrice=price, @rn,
                    if(@prevPrice:=price, @rn + 1, @rn + 1)) rn
  from test cross join (select @rn:= 0, @prevPrice:= null) t
  ) t
where rn > 2

• More Fiddle

Answer 2

您需要一个临时表：

SELECT SUM(Price) FROM test WHERE Item NOT IN (
    SELECT * FROM (
        SELECT Item FROM test ORDER BY Price DESC LIMIT 2
    ) AS tmp
)