Single-table unique query with: DISTINCT
Unique query for multiple tables: Group by
Distinct when querying multiple tables, the LEFT join is also valid, the 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 examples:
The structure of the table is as follows:
ID Name
1 A
2 b
3 C
4 C
5 b
The basic table structure is probably like this, 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 the name does not duplicate all the data, it must use distinct to remove unnecessary duplicate records.
select DISTINCT name from table
the results obtained are:
Name
A
C
It seems to be working, but what I want to get is the ID value? Change the query statement:
SELECT DISTINCT name, ID from table
The result will be:
ID Name
1 A
2 b
3 C
4 C
5 b
Why didn't distinct play a role? 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 will change the query statement:
Select ID, distinct name from table
Unfortunately, you can't get anything except the wrong message, distinct must be placed at the beginning. Is it hard to put distinct in the where? Try, and still give an error.
Tried a half-day other can think of methods also not, finally in the MySQL manual found a usage, with GROUP_CONCAT (distinct name) with group by name to achieve the function I need, excited, God save me also, quickly try.
Error, Depressed!
Even the MySQL handbook was a mess for me, giving me hope first and then pushing me down again.
Again careful check, Group_concat function is 4.1 support, Halo, I 4.0. No way, upgrade, the level of a try, success.
It's finally done, but it's going to require a client upgrade.
A sudden flash of a mind, since the GROUP_CONCAT function can be used, that other functions can do?
Quickly use the Count function a try, success, cost so much time, originally so simple.
now release the full 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
The last one is superfluous, do not need to control on the line, to achieve.
The original MySQL so stupid, gently put him fooled past, now take out hope that we do not be the problem toss.
By the way, group by must be placed before the order by and limit, otherwise it will be reported as 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.ni D INNER JOIN {node_images} ni on Ni.nid=n.nid where Tn.tid in ('. Implode (', ', $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.
