Group group to remove duplicate data
/**
* Erase duplicate import data from the same topic
* @author Tanteng
* @date 2014.07.27
*/
Public Function fuck_repeat () {
Set_time_limit (0);
$sql = "Select" id ' from ' v95_special_content ' GROUP by ' specialid ', ' Curl ' has COUNT (' curl ') >1 ";
$result = $this->db->query ($sql);
while ($r = Mysql_fetch_assoc ($result)) {
$ids [] = $r [' id '];
}
$ids = Implode (",", $ids);
if (! $ids) {
ShowMessage (' No duplicate data! ", http_referer); exit;
}
$sql 2 = "DELETE from ' v95_special_content ' where ' id ' in ($ids)";
$this->db->query ($sql 2);
ShowMessage (' executed successfully, deleted '. $this->db->affected_rows (). ' Duplicate data! ', http_referer);
}
Let's analyze This SQL statement:
SELECT ' id ' from ' v95_special_content ' GROUP by ' specialid ', ' Curl ' has COUNT (' curl ') >1
This group is grouped according to two fields, respectively, specialid and curl, which means that the two fields are the same as condition queries, having is the condition of the group statement, the equivalent of where, and the following is the count of such a number of bars.
The use of MySQL group grouping enables the removal of duplicate data from multiple fields in the database.
add: Of course, you can also use the DISTINCT keyword to filter out the extra duplicate records and keep only one
Table
ID Name
1 A
2 b
3 C
4 C
5 b
Select DISTINCT name from table
The results obtained are:
Name
A
B
C
It seems to be working, but what I want to get is the ID value? Change the query statement.
SELECT *, COUNT (distinct name) from table group by name
Results:
ID Name count (distinct name)
1 a 1
2 B 1
3 C 1