Finding Duplicate Entries in a table

问题:

I have a table with the following fields in Oracle 10g.

TABLE_1

account_no | tracking_id | trans_amount

Each account_no can have multiple tracking Id's and transaction amounts.

How do i query out the duplicate entries of account_no where tracking lies between 1 and 1000, and the corresponding trans_amount ?

Many thanks in advance for your help,

novice.

回答1:

Try the following query:

SELECT account_no, tracking_id, trans_amount
FROM TABLE_1
WHERE
account_no IN
    (
    SELECT account_no FROM TABLE_1
    WHERE tracking_id >= 1 AND tracking_id <= 1000
    GROUP BY account_do
    HAVING COUNT(*) > 1
    )

Explanation of the subquery: it finds all the account_no's for which there are more than 1 such that its tracking_id is between 1 and 1000.

I hope that's what you meant.