Parsing MySQL: Single table distinct, multiple table group by query to remove duplicate records

The unique query for the

single table uses: DISTINCT
multiple table unique query with: GROUP BY
distinct query multiple tables, the LEFT join is also valid, full connection is invalid,
when using MySQL, There are times when you need to query for records that are not duplicated in a field, although MySQL provides a distinct keyword to filter out excess duplicates, but it often only uses it to return the number of records that are not duplicates, rather than using it to return all the values of a record that is not duplicated. The reason is that distinct can only return its target field, and can not return to other fields, with distinct can not be resolved, I only use the double loop query to solve, and so for a very large amount of data on the station, will undoubtedly directly affect the efficiency.
Let's take a look at the following example: The structure of the
table is as follows:
ID name
1 a
2 b
3 C
4 C
5 B
The structure of the basic table this is just a simple example , the actual multiple table query and so on will be much more complicated.
For example, I want to use a statement query to get all the data that name does not duplicate, then you must use distinct to remove the extra duplicate records. The result of the
select distinct name from table
is that the
name
a

C
seems to have worked, but I What do you want to get the ID value? Change the query statement bar:
SELECT DISTINCT name, id from table
result will be:
ID name
1 a
2 b
3 C
4 C
5 B
Distinct why didn't it work? The effect is actually up, but he also acts two fields, that is, the ID must be the same as the name will be excluded.
We change the query statement:
Select ID, distinct name from table
Unfortunately, you don't get anything except the error message, Distinct must be placed at the beginning. Is it hard to put distinct in the where? Try, and still give an error.

has triedHalf-day Other methods can be thought of, and finally in the MySQL manual to find a usage, with GROUP_CONCAT (distinct name) with the group by name to achieve the function I need, excited, God save me also, quickly try.
Error, depressed!
Even the MySQL manual was a mess for me, giving me hope first and then pushing me down again.
Look again, the Group_concat function is 4.1 support, Halo, I 4.0. No way, upgrade, the level of a try, success. The
is finally done, but in this way, customers must also be asked to upgrade.
Suddenly a sudden-Turing, since the Group_concat function can be used, that other functions can do?
quickly use the Count function to try, success, cost so much time, originally so simple.
now emits the full statement:
SELECT *, COUNT (distinct name) from table group by name
Result:
ID Name count (distinct name)
1 a 1
2 B 1
3 C 1
The last one is superfluous, no matter what you do, the purpose is to achieve.
The original MySQL so stupid, gently put him to cheat the past, now take out hope that we do not let this problem toss.
by the way, group by must be placed before the order by and limit, or it will be an error.
say the actual example of GROUP by:

Copy Code code as follows:


$sql = ' Select DISTINCT n.nid,tn.tid,n.title,n.created,ni.thumbpath from {Term_node} tn INNER JOIN {node} n on N.nid =tn.nid INNER JOIN {node_images} ni on Ni.nid=n.nid where Tn.tid (', ', $tids). ORDER by N.nid DESC ';


$res = Db_query ($sql);


$t _data = Array ();


while ($r = Db_fetch_array ($res)) {


Print_r ($R);


}

When using this query statement, there will always be two identical nid, such as the following results

Copy Code code as follows:


Array


(


[created] => 1215331278


[nid] => 1603


[Tid] => 32


[title] => summer wedding Green qin drink DIY


[Thumbpath] => files/node_images/home-77.1_tn.jpg


)


Array


(


[created] => 1215331278


[nid] => 1603


[Tid] => 32


[title] => summer wedding Green qin drink DIY


[Thumbpath] => files/node_images/003_primary_tn.jpg


)

The above use distinct also no use, actually is useful, but I think the query structure nid is the only.
Finally, the group by

Copy Code code as follows:


$sql = ' Select


N.nid,tn.tid,n.title,n.created,ni.thumbpath from {Term_node} tn INNER


Join {node} n on N.nid=tn.nid INNER join {node_images} ni on


Ni.nid=n.nid where Tn.tid in ('. Implode (', ', $tids). GROUP by


N.nid DESC ';


$res = Db_query ($sql);


$t _data = Array ();


while ($r = Db_fetch_array ($res)) {


Print_r ($R);


}

I got the NID is the only one.