concat usage of SQL

This is a Java communication group in the interview of a netizen sent over the pen test, I think the topic of the hypothetical condition should be a letter corresponding to the smallest number only one.

The first step in thinking is to find a child table S1:select name,min (number) from the USERS group by name. Of course, this can be followed by the having count (name) >1; My solution is to choose not to add, meaning that even if a letter corresponds to a number, also take him out to put into this sub-table.

The second step is more troublesome, how to delete the extra elements? If you are using delete from the USERS where name in (select name from S1 (the name of the child table)) and number not in (select number from S1). This is obviously not possible, because there is no number to compare the table, if the element like a is 4 because it is equal to the minimum of C, it will not be deleted.

The key is to group the comparison

Look at the SQL statement Library, suddenly found that there is a concat keyword, concat (column name 1, column name 2) is the column name 1 and the column name 2 is identified to form a new column name. Just used here, 4,a and 4,c with number in bad distinction, 4a and 4c This is always good to distinguish it.

SELECT * FROM T_ss WHERE CONCAT (Name,number) isn't in (select CONCAT (NAME,MN) from ((select Name,min (number) as MN from T_SS GROUP by NAME) (as S1))

Note: My table is T_SS, delete the experiment is irreversible, with select is the same

concat usage of SQL