Postgres database id the same but the name is different from dirty data, how to filter?-CodePudding

For example:
Id name
4114811999123201234 zhang
4114811999123201234 scattered
4114811999123201234 Zhang San

CodePudding user response:

Nature is a data cleaning problems,,,
1, define the rules of dirty data
Such as: an id number can only corresponds to a name

2, according to the rules for writing SQL, dirty data
Such as: SELECT * FROM table_name WHERE "id number" IN
(SELECT "id", "name" FROM table_name GROUP BY "id", "name" HAVING the COUNT (*) & gt; 1) AS dirty_data

3, to deal with it

CodePudding user response:

Rechecking to heavy classic statement exist

 
Select * from idcard t1 
Where sexists (
Select 1 from idcard t2 
Where a t1. Id_card=t2. Id_card 
And t1. Name<> T2. Name)