Hello, is there a table, 100000 data, to query the qualified, but because the data is too big, can't verify whether accurate, but I use in my condition, a new table, the data content basic tables, but according to the previous query result is wrong!

Above is a table of contents, of course I have a id number in the table it can add a id field
I want the result is that
Find duplicate records, but " zhang SAN is not in conformity with the ", as long as in the KT field "opened" whether a few records even if success!
I want the result is the bill, fifty and KT is "N" not seen "open" to find out! Would, of course, also there is a single "N" is also a need to look out!
SELECT * FROM the peo WHERE (id, people_no, people_name) IN (SELECT id, people_no, people_name FROM the peo WHERE kt="\ \ N" GROUP BY people_no, people_name HAVING the COUNT (*) & gt;=1);
Thank you for your attention!

CodePudding user response:

You want is this effect



My SQL is wrote

 
Select * from 
Sid (select a. *, b.i d 
The from the peo a 
Left the join (select * from the peo where peo. Kt='open') on Amy polumbo eople_no b=p. eople_no and Amy polumbo eople_name=p. eople_name) 
A GROUP BY Amy polumbo eople_no, Amy polumbo eople_name having a.s id is null 

CodePudding user response:

Yes, thank you very much !

CodePudding user response:

How to add a column sid? What is your idea, please?

CodePudding user response:

The

reference 3 floor smilelxj response:

how to add a column sid? What is your idea, please?

Using the left join, on the left side of the table data is not associated with a table on the right side b data, also displays, but the b table data b.i d filled with null, took this id alias sid, behind the name used for judgment,
No problem, logical skills is good also, just can't understand why is so complicated,

CodePudding user response:

Seems to feel a bit wrong, the results should be zhang SAN (open) the records were sifted out, then you should do with the name conditions, there should be no id, id + name should only be associated with "open" the record, zhang SAN correlation is less than other null records, together with the other,

CodePudding user response:

reference happy 4th floor response:

Quote: refer to the third floor smilelxj response:

How to add a column sid? What is your idea, please?

Using the left join, on the left side of the table data is not associated with a table on the right side b data, also displays, but the b table data b.i d filled with null, took this id alias sid, behind the name used for judgment,
No problem, logical skills is good also, just can't understand why is so complicated,

The above said is right you can choose to run the code

 
Select * from 
Sid (select a. *, b.i d 
The from the peo a 
Left the join (select * from the peo where peo. Kt='open') on Amy polumbo eople_no b=p. eople_no and Amy polumbo eople_name=p. eople_name) 
A GROUP BY Amy polumbo eople_no, Amy polumbo eople_name having a.s id is null 

First to get the full table of data and then according to the user number and name to related users, have been opened at this time of the associated condition sid can have value, the sid field just do a logo, this has a value according to the user number and name to open to the corresponding records, then the data as a result on the names and Numbers of user groups when the sid has no value, means that the user does not have opened

CodePudding user response:

The corresponding wrong you first run the subquery see result is below

CodePudding user response: