MySQL deduplicated display of specified field information

When MySQL uses the select distinct name from table Statement of MYSQL to deduplicate a specified field, only the information of the name field after deduplication is output, the hope is to output the information about the examinee's ID, examination time, and examination score. Use

1select distinct name, sorce from table

 

The result shows that deduplication does not work, because MYSQL considers that sorce and name must be repeated at the same time to be removed. The final solution is as follows:

1select *, count(distinct name) from table group by name

 

If the SQL statement has conditions such as limit and order by, it must be placed behind group. This achieves both de-duplication and the ability to output more field information. SELECT statements to remove repeated information of a field, such as: table Name: table

Id uid username message dateline 1 6 a 111 1284240714 222 (timestamp) 2 6 a 1268840565 444 3 8 B 1266724527 555 4 9 c 1266723391

 

Execute the statement (remove duplicate username field information and sort by Time ):

SELECT * FROM table a INNER JOIN ( SELECT max( dateline ) AS dateline FROM table GROUP BY uid ) b ON a.dateline = b.dateline GROUP BY id ORDER BY a.dateline DESC 

 

Result:

Id uid username message dateline 1 6 a 111 1284240714 (timestamp) 3 8 B 444 1266724527 4 9 c 555 1266723391

 

This statement is used to display the latest record information. A certain information (such as a user) is not allowed to appear multiple times (more than once) in a region ). Note: The efficiency problem begins to use this statement:

select * from table where dateline IN ( select max(dateline) from table GROUP BY uid ) ORDER BY dateline DESC 

 

IN: when processing a large amount of data, there is no efficiency at all. Therefore, it is optimized to inline, and the computation speed is more than 6 times faster... After the query continues, the index is added ~~