删除所有值比第二高值低 5 倍的记录 [英] Delete all records having value 5x lower than the second highest value

问题描述

我有一个包含两个字段的表(价格)，代码(字符)和价格(十进制).我需要找到具有相同代码的所有记录，价格比两个最高价格低 5 倍或更少.

I have a table (prices) with two fields, code (char) and price (decimal). I need to find all records with same code, having price 5x lower or less then two highest prices.

E.G.在这种情况下，我希望删除 id=1:

E.G. In this case I wish to delete id=1:

id   code   price
1    1001   10
2    1001   101
3    1001   40
4    1001   201
5    1002   122
6    1002   50

推荐答案

DELETE
FROM myTable
WHERE ID IN (
    SELECT *
    FROM (
        SELECT t2.id
        FROM myTable t2
        WHERE EXISTS (
            SELECT 1
            FROM myTable t3
            WHERE t3.code = t2.code
            AND t3.price > t2.price * 5
            HAVING COUNT(*) > 1
            )
    ) t
)
;

这篇关于删除所有值比第二高值低 5 倍的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！