About SQL and MySQL some understanding that aliases cannot be invoked _mssql

In writing SQL, because there are some statements alias can not be invoked, Baidu for a reason, the original is due to the different alias mechanism caused. To avoid making the same mistake the next time, find the information on the Internet today to summarize, SQL and MySQL execution order, found that the internal mechanism is the same. The biggest difference is in the reference to the alias.

first, SQL execution order
(1) from
(3) Join
(2) on
(4) where
(5) Group BY
(6) Avg,sum ....
(7) having
(8) Select
(9) Distinct
(a) Order by

In this order, it is not difficult to find that all the query statements are executed from the beginning, and in the course of execution each step generates a virtual table for the next step, which will be the input to the next execution step.
Step one: First execute a Cartesian product on the first two tables in the FROM clause, then generate the virtual table VT1 (select a relatively small table to do the underlying table)
Step two: Next is to apply the on filter, where the logical expression is applied to each row in the VT1, filtering out rows that satisfy on logical expressions, and generating virtual tables VT2
Step three: If the outer join then this step will add the outer row, left outer jion the left table in the second step of the filter to add in, if it is right outer join then the right-hand table in the second step of the filtered row to add in, so that the virtual table generated VT3
Fourth: If the number of tables in the FROM clause is more than two tables, then the VT3 and the third table are concatenated to compute the Cartesian product, generating the virtual table, which is a repeat step of 1-3, resulting in a new virtual table vt3.
Step Fifth: Apply the Where filter, reference the where filter to the virtual table that was produced in the previous step, and generate the virtual table VT4, here's a more important detail to say, there's a confusing question about queries that contain outer JOIN clauses, Do you want to specify the logical expression in the on filter or the where filter? The biggest difference between on and where is that if you apply a logical expression in on, you can also add the removed row back in the third step outer join, and the removal of the where is the final. To give a simple example, there is a student table (class, name) and a score sheet (name, grade), I now need to return to an X class of all the students, but this class has a few students lack of examination, that is, in the score table is not recorded. To get the results we expect, we need to specify the relationship between the student and the score sheet in the ON clause (student. Name = grade. Name) So, do we find that when we perform the second step, the record of the students who have not taken the exam will not appear in the VT2 because they are filtered by the logical expression on. But we can use the left outer join to bring back the students who have not taken the exam in the left-hand table, because we want to return all the students in the X class, if the students are applied in on. class = ' x ' words, then in leave outer join You will not be able to retrieve all the records of the students in the X class, so you can apply the student only in the where filter. class = ' x ' because its filtration is final.
Step Sixth: The GROUP BY clause combines the unique values in to a group to get the virtual table VT5. If group by is applied, then all subsequent steps can only be VT5 columns or aggregate functions (count, SUM, AVG, and so on). The reason is that the final result set contains only one row for each group. Please keep this in mind.
Step seventh: Use the cube or Rollup option to generate a vt5 for the vt6.
Step Eighth: Apply having a filter to generate VT7. The having filter is the first and only filter that is applied to the grouped data.
Step nineth: Process the SELECT clause. Filter the columns that appear in the select in Vt7. Generate Vt8.
Step Tenth: Apply the DISTINCT clause, vt8 to remove the same row, generate Vt9. In fact, if the GROUP BY clause is applied, then distinct is redundant, as is the case when grouping is a group of unique values in a column, and only one row for each group is returned, so the records will be different.
Step 11th: Apply the ORDER BY clause. Sort the vt9 by Order_by_condition, at which point a cursor is returned, not a virtual table. SQL is based on the theory of the set, the collection does not order his rows in advance, it is just a logical set of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains a logical organization for a particular physical order. This object is called a cursor. Because the return value is a cursor, a query with an ORDER BY clause cannot be applied to a table expression. Sorting is very cost-unless you have to sort it, it's best not to specify an order by, and finally, in this step, is the first and only step that can use aliases in the select list.
Step 12th: Apply the top option. At this point the result is returned to the requester, the user.

Second, the implementation of MySQL sequence
SELECT statement definition
A completed SELECT statement contains several optional clauses. The SELECT statement is defined as follows:
SQL code

Copy Code code as follows:

<select clause> [<from clause>] [<where clause>] [<group by Clause>] [

The SELECT clause is required, and other clauses such as the WHERE clause, the GROUP by clause, and so on are optional.
In a SELECT statement, the order of clauses is fixed. For example, the GROUP BY clause is not located in front of the WHERE clause.

SELECT statement Execution order
The order of the sentences in the SELECT statement is different from the input order of the neutron sentences in the SELECT statement, so it is not performed from the SELECT clause, but rather in the following order:
Start->from clause->where clause->group BY clause->having clause->order BY clause->select clause-&GT;LIMIT clause-> final result
Each clause is executed to produce an intermediate result for use in the next clause, if there is no one, skip
In contrast, MySQL and SQL are basically the same sequence of execution, the standard sequence of SQL statements are:

SQL code

Copy Code code as follows:

Select Candidate Name, Max (total score) as Max Total

From Tb_grade

Where candidate name is not NULL

Group BY candidate name

Having Max (total score) > 600

ORDER by Max Total Score

In the example above, the SQL statements are executed in the following order:

(1). First execute FROM clause, assemble data source data from Tb_grade table

(2). Executes a WHERE clause to filter all data in the Tb_grade table that is not NULL

(3). Execute GROUP BY clause, Group Tb_grade table by "Student name" column

(4). Compute the MAX () aggregate function and find out the largest number in the total score by "Total Score"

(5). To perform a HAVING clause, the total score of the course is greater than 600.

(7). Execute the ORDER BY clause to sort the final result by "Max score".

The following is an example of the root cause of the alias being unavailable:

Example One

SQL code

Copy Code code as follows:

Select M.mname as Username,
AVG (S.score) as Rscore,
(case
When AVG (S.score) < Then
Poor
When AVG (S.score) >= and AVG (s.score) <= Then
Good
When AVG (S.score) > Avg (s.score) <= Then
Excellent
Else
' Not legal '
End
From score s, Student m
where S.mid = M.mid
GROUP BY M.mname
ORDER BY Rscore Desc;

If the case inside the AVG (S.score) to alias Rscore in SQL run on the error

Example Two

SQL code

Copy Code code as follows:

Select COUNT (*) as C from score group by mid has have count (*) >2;

If the having inside of the count (*) replaced by alias C, this sentence in the SQL into the wrong, in the MySQL is correct, baffled, give a friend to point out, finally understand the pass, the following is the description:
Order of execution of GroupBy and Having,where,orderby statements:
Finally, explain the order of execution of the Groupby,having,where,orderby several statements. 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 order: Where,groupby,having,orderby. The first place is to delete records that do not meet the criteria in the original record, then group the filtered views by the grouping criteria specified after the GroupBy keyword, and then filter the records that do not meet the criteria after the group view, based on the filter criteria specified after the HAVING keyword. Then sort the view 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:

SQL code

Copy Code code as follows:

SELECT Fruitname,productplace,price,id as Ide,discount fromt_test_fruitinfo WHERE ORDER by IDE

You can use the IDE only in the order BY statement, and you can use an ID only if you need to refer to a column name in other conditional statements, rather than using the IDE.