How do I use distinct to cancel duplicate rows in oracle?

First of all, a bit vague about your needs, cancel the duplicate line of the c id, and then also show the c id, s id. The above two SQL statements don't know if they meet your needs, for example.

select

distinct

c_id，s_id

fromxskc

This statement eliminates records where the c id and s id are the same at the same time, so there will still be duplicates for the c id alone.

sql>

descdup

namenull?type

idnumber

namevarchar2(10)

sql>

select

fromdup;

idname

aaaaaabbb

bbbsql>

select

distinct

id,name

fromdup;

idname

aaabbbaaa

bbbsql>

select

distinct

idfrom

dup;id

So anyway, if you're going to show the C ID and the S ID, the C ID is probably going to be the same, unless you have other restrictions.

If you want to cancel duplicate rows for an individual field, you can use a groupby statement for that field, for example:

select

c_id,min(s_id)

fromxskc

groupby

c_iddistinct

It's about making sure that the whole row of data is not duplicated, as other friends have already explained.

Just add distinct before the duplicate column names.

Like what. select

distinct

snofrom

student

The steps to de-duplicate the data queried by Oracle are as follows:

1. First of all, let's look at the duplicate data in the table.

2. Then I use the distinct removal function to query out the duplicate data.

3. Then we create a new table and insert the duplicate data into the new table.

4. Finally, use truncate to clear the data in the original table.

5. Insert duplicate data in the new table into the original table to achieve the effect of deduplicating data.

Add the distinct field to the end of the select keyword.

This problem is to delete duplicate records in the table, and the SQL statement of the query is as follows:

select id,name from (select id,name,row_numer() over(partition by id,name order by id,name) rn from tablea)

where rn>1;

The deleted SQL statement is as follows:

delete tablea where rownum = (select rownum

from (select id,name,row_number() over(partition by id,name order by id,name) rn

from tablea )

where rn > 1);

Oracle to remove duplicate rows using the following method:

For example, the test table contains the following data:

To remove duplicate data from name, you can use the following statement to delete redundant data:

delete from test where id not in (select max(id) from test group by name);

Post-execution result:

Query results, or what?

If you want to query the result, you can assume distinct.

select distinct * from tabname;

Everything else depends on the situation.

If you want to cancel duplicate rows for an individual field, you can use a groupby statement for that field, for example:

select c_id, min(s_id)from xskc

group by c_id

distinct is to ensure that the entire row of data is not duplicated, as other friends have already explained.

First of all, a bit vague about your needs, cancel the duplicate line of the c id, and then also show the c id, s id. The above two SQL statements don't know if they meet your needs, for example.

select distinct c id, s id from xskc, this statement eliminates the same record as c id and s id at the same time, so there will still be duplicates for c id alone.

sql> desc dup

name null? type

id number

name varchar2(10)

sql> select * from dup;

id name

1 aaa2 aaa

1 bbb3 bbb

sql> select distinct id,name from dup;

id name

1 aaa1 bbb

2 aaa3 bbb

sql> select distinct id from dup;

id---12

3So anyway, if you want to show the C ID and the S ID, the C ID will basically be duplicated, unless you have other conditions.

Just add distinct before the duplicate column names.

For example, select distinct sno from student

distinct can only be used to eliminate duplicate rows, not duplicate fields.

There are no association conditions between the person table and the team person table, if all the association conditions are written, there will be no duplicate data in all fields, and there is no need for distinct

You must have not completed the condition, the condition of the where clause has been completed.

I didn't look closely, so let's remove rownum as rw first, and then distinct?

Use in or exists

However, relatively speaking, the speed of using in is slow, you can try to use exist (if there is a lot of data, it will be more obvious to feel, the data is very small, almost no difference).

1。Use in

select service, name, notefrom table01

where service not in (select service from table02)

2。Use exists

select service, name, notefrom table01

where not exists (select service from table02)

distinct to remove duplicate keywords.

select distinct fieldname,