Detailed explanation of two mysql weighting methods and sample code

mysql Weight Removal

Method 1:

When using MySQL, it is sometimes necessary to query for records that are not duplicated in a field. Although mysql provides the keyword distinct to filter out redundant duplicate records and keep only one, it is often used to return only the number of records that are not duplicated, rather than all the values that are not duplicated.The reason is that distinct can only return its target field, not other fields

Let's start with an example:

   table
  id name
  1 a
  2 b
  3 c
  4 c
  5 b

This is a simple example of the library structure, which is much more complex.

For example, if I want to query all data that name does not repeat with a single statement, I must use distinct to remove redundant duplicate records.

select distinct name from table

The result is:

 name
  a
  b
  c

It seems to be working, but what do I want to get is the id value?Change one query statement:

select distinct name, id from table

The result would be:

 id name
  1 a
  2 b
  3 c
  4 c
  5 b

Why doesn't distinct work?The effect works, but he works on two fields at the same time, that is, id must be the same as name before it can be removed...

Let's change the query statement again:

select id, distinct name from table

Unfortunately, you won't get anything but the error message. distinct must be at the beginning.Can't we put distinct in the where condition?Can make an error.

The final useful statements are as follows:

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 redundant, no matter what, the goal is achieved.

Oh, yes, by the way, group by must be placed before order by and limit or an error will occur...OK

Summary statement: select *, count (distinct name), from (nested statement such as select * from table...) group by name

Method 2:

Utilize group by

SELECT * FROM( 
select * from customer where user=( 
  SELECT source_user from customer WHERE user='admin') UNION ALL select * from customer where user=( 
  select source_user from customer where user=( 
    SELECT source_user from customer WHERE user='admin')) union ALL select * from customer where user=( 
  select source_user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))) UNION ALL select * from customer where source_user=(/* My Online user*/ 
  select user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))) union all select * from customer where source_user=(/* My Online Online Online user*/ 
  select user from customer where user=( 
  select source_user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))))) as alias group by user; 

Be aware of aliases or errors. Pack 1 outside the where statement before using group by to redo it.

Thank you for reading, I hope to help you, thank you for your support on this site!