在Access中删除SQL中的重复项 [英] Deleting duplicates in SQL in Access

问题描述

我有一个表，该表由id(key)，符号，方向，范围，价格，百分比列组成.我想删除符号，方向，价格和百分比相同且范围最小的数据.我该怎么办?

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?

我一直在尝试更改以下语句:DELETE FROM aw WHERE id not in (select min(id) from aw group by symbol, direction, price, percent);以使某些事情起作用.

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.

推荐答案

以下是 aw 表的示例数据.根据您的描述，我认为您希望舍弃ID值为2、3和5的行.

Here is sample data for the aw table. Based on your description, I think you want to discard the rows with ID values of 2, 3, and 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组合的最小范围值.

Determine the minimum range value for each combination of symbol, direction, price, and percent.

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.

Determine the minimum ID for each of those minimum ranges.

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 中删除其ID值未包含在 qryMinID_forMinRanges 中的行.但是，首先尝试使用此SELECT查询，以确认您定位的是正确的删除记录.

So qryMinID_forMinRanges should represent the rows you want to keep. Ultimately you will delete the rows from aw whose ID values are not included in qryMinID_forMinRanges. But first try this SELECT query to confirm you're targeting the correct records for deletion.

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);

...这给了我这个结果集:

... which gives me this result set:

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查询.

So, if that looks correct, change it to a DELETE query.

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

DELETE之后，db引擎不需要*.但是，如果要使用查询设计器的预览，则需要给它一个字段规范以显示.

The db engine doesn't require the * after DELETE. But if you want to use the query designer's preview, you need to give it a field spec to display.

如果我在此处弄错了任何详细信息，请采取预防措施以保护您的数据.备份很好. :-)

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

这篇关于在Access中删除SQL中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！