What are the problems with distinct in mysql query statement

This article mainly explains "what are the problems with distinct in the mysql query sentence", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the problems with distinct in the mysql query sentence"?

What are the problems with distinct in mysql query statement

The reason is that distinct returns the number of records that are not duplicated, rather than using it to return all values of records that are not duplicated.

That is, distinct can only return its target field, but not other fields.

For example:

SELECTDISTINCTmac,ipfromip+-+-+ | mac | ip | +-+-+ | abc | 678 | abc | 123 | def | 456 | | abc | 12 | +-+-+

He still won't change! Because the effect of the above statement is that it acts on two fields, that is, only those with the same mac and ip can be excluded.

Finally, there is no way, use groupby to have a look!

Check out the mysql manual! connt (distinctname) can be implemented with groupby.

A count function implements the function I want.

Select*,count (distinctmac) fromipgroupbymac;+-+ | mac | ip | count (distinctmac) | +-+ | abc | 678 | 1 | def | 456 | 1 | +-+

Basically realize my idea!

So how do you implement a table with two fields mac and ip, and how do you find all the records with the same mac but different ip?

Mysql > select*fromip;+-+-+ | mac | ip | +-+-+ | abc | 123 | def | 456 | ghi | 245 | abc | 678 | def | 864 | abc | 123 | ghi | 245 | +-+ 7rowsinset (0.00sec) mysql > SELECTDISTINCTa.mac,a.ip- > FROMipa,ipb- > WHEREa.mac=b.macANDa.ipb.ipORDERBYa.mac;+-+-+ | mac | ip | +-+ | abc | 678 | abc | 123 | def | 864 | def | 456 | +-+-+ 4rowsinset (0.00sec)

What are the problems with the case of mysql queries

Mysql queries are case-insensitive by default, such as:

Selectfromtable_namewherealike'a'selectfromtable_namewherealike'a'selectfromtable_namewherealike'a'selectfromtable_namewherealike'a'

The effect is the same.

To make mysql queries case-sensitive, you can:

Selectfromtable_namewherebinaryalike'a'selectfromtable_namewherebinaryalike'a'selectfromtable_namewherebinaryalike'a'selectfromtable_namewherebinaryalike'a'

It can also be identified when creating a table.

Createtabletable_name (avarchar (20) binary)

At this point, I believe you have a deeper understanding of "what are the problems with distinct in the mysql query sentence?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!