Database Learning 3 Distinct Group by__ Database

1 Effect range of the Distinct

Let's take a look at the following examples:

ID	name
1	A
2	B
3	C
4	C
5	B

The library structure is probably like this, this is just a simple example, the actual situation 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

how distinct didn't work. The effect is up, but he also works with two fields, that is, the ID must be the same as name to 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. It is difficult to put distinct in the Where condition. Can, still error.

The following method works:

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, no need to control on the line, to achieve

How much does Sql distinct know

GROUP by must be placed before the order by and limit, or it will be an error: 2 Group by and having, where the order BY statement is executed:

Description of the group by, have, where, order by several statements of the execution sequence. An SQL statement tends to produce multiple temporary views, so the order in which these keywords are executed is important because you have to understand whether the keyword operates in the field before the corresponding view is formed or in the form of a temporary view, which is especially important when using an alias view.

The keywords listed above are executed in the following sequence:where, Group by, have,order by. The first place is to delete records that do not meet the criteria in the original record (so you should try to filter out records that are not eligible in the WHERE statement so that you can reduce the number of groupings), and then group the filtered views by the grouping criteria that you specify after the group by keyword. The system then filters the records that do not meet the criteria after the grouped view according to the filter criteria that are specified after the HAVING keyword, and then sorts the views by the order BY statement so that the final result is generated. In these four keywords, only the column names of the final view can be used in the order by statement, such as:

SELECT Fruitname,productplace,price,id as Ide,discount from
t_test_fruitinfo
WHERE (productplace=n ' ") ORDER BY
IDE

You can use the IDE only in the order BY statement, and you can only use IDs if you need to reference column names in other conditional statements, not the IDE.

GROUP by Summary
use of groupby in SQL 3 join in and where

Left join on and placement of where condition, left join failure problem