The table name: the customer table
Fields are as follows:
Customer ID, name, sex
From the business, the same customer ID, should be the same name and gender, but there are dirty data in the table, need to take look at how many, but the same customer ID, name and gender different
EX:
Customer ID, name, sex
1 zhang SAN men
1 zhang SAN men
2 threes male
2 li si men
2 li si female
3 threes male
3 li si men
3 li si female
Data as those listed above, the customer ID of 1 data is correct, their names, sex are the same
But customer ID for 2, 3, their name, gender, this should not be a man, so the customer ID is the same dirty data
I want to be as a result, it is the customer ID as the number of dirty data,
Which is the result: 2
(* customer ID: 2, 3, the two ID is the dirty data)
I try to write a write
Select count (*) from (
Select the customer ID, name, sex
From the customer table as t inner join (
Select the customer ID, name, sex
From the customer table
Group by customer ID, name, sex) as t1
On t. customer ID=t1. The customer ID and (t. name & lt;> T1. Name OR t. gender & lt;> T1. Gender)
Group by customer ID, name, sex)
)
But I feel I should not write, for advice
CodePudding user response:
Select count (1) the from(select customer ID
From the customer table
Group by customer ID having count (distinct name | | gender) & gt; 1
) t;