Question

I have a table that consists of the columns id(key), symbol, direction, range, price, percent. I would like to delete the data where the symbol, direction, price, and percent are the same that has the lowest range. How would I accomplish this?

I have been trying to alter this statement: DELETE FROM aw WHERE id not in (select min(id) from aw group by symbol, direction, price, percent); to get something to work.

Answer 1

此处为aw表格的样本数据。根据您的描述,我认为你想抛弃“> 数值为2、3和5的行文”。

ID symbol direction range price percent
1  a      x            15    10       5
2  a      x            20    10       5
3  b      y            40    50       5
4  b      y            10    50       5
5  a      x            15    10       5

确定每个组合的最低范围值:symbol,direction,price和percent。

<>strong>qryMinRanges:

SELECT
    aw.symbol,
    aw.direction,
    aw.price,
    aw.[percent],
    Min(aw.range) AS MinOfrange
FROM aw
GROUP BY
    aw.symbol,
    aw.direction,
    aw.price,
    aw.[percent];

......结果如下:

symbol direction price percent MinOfrange
a      x            10       5         15
b      y            50       5         10

确定每个最低幅度的最低<代码>ID。

qryMinID_forMinRanges:

SELECT
    q.symbol,
    q.direction,
    q.price,
    q.[percent],
    q.MinOfrange,
    Min(aw.ID) AS MinOfID
FROM
    qryMinRanges AS q
    INNER JOIN aw
    ON
        (q.MinOfrange = aw.range)
        AND (q.[percent] = aw.[percent])
        AND (q.price = aw.price)
        AND (q.direction = aw.direction)
        AND (q.symbol = aw.symbol)
GROUP BY
    q.symbol,
    q.direction,
    q.price,
    q.[percent],
    q.MinOfrange;

......结果如下:

symbol direction price percent MinOfrange MinOfID
a      x            10       5         15       1
b      y            50       5         10       4

因此,qryMinID_forMinRanges 应当代表你希望保持的行文。最终,您将从aw删除其发展价值未被列入qryMinID_forMinRanges的浏览量。但是,首先尝试使用<条码>。询问你是否重新确定删除的正确记录。

SELECT
    aw.ID,
    aw.symbol,
    aw.direction,
    aw.range,
    aw.price,
    aw.[percent]
FROM aw
WHERE aw.ID Not In
    (SELECT MinOfID FROM qryMinID_forMinRanges);

......这给我带来了这一结果:

ID symbol direction range price percent
 2 a      x            20    10       5
 3 b      y            40    50       5
 5 a      x            15    10       5

因此,如果看上去正确的话,它就改为DELETE。

DELETE *
FROM aw
WHERE
    (((aw.ID) Not In
        (SELECT MinOfID FROM qryMinID_forMinRanges)));

db发动机在DELETE之后需要*。但是,如果你想利用问询设计师的预约,那么你需要给它一个实地观。

Please take precautions to safeguard your data in case I screwed up any details here. Backups are good. :-)

Answer 2

使用<代码>exists 条款:

DELETE FROM aw 
WHERE 
    exists (
        select
            1
        from
            (select symbol, direction, price, percent, min(range) as minrange from aw
            group by symbol, direction, price, percent) aw2
        where
            aw2.symbol = aw.symbol
            and aw2.direction = aw.direction
            and aw2.price = aw.price
            and aw2.percent = aw.percent
            and aw2.minrange = aw.range
    )

友情链接