After one year of work, I always felt that I was missing something. Among them, I feel the strongest about SQL Server, so now I start to learn again.
Click to download the database script.
See the following SQL statement.
1 Select Custid, Count (Custid) As Counts 2 From Orders 3 Where Custid > 12 4 Group By Custid 5 Having Count (Custid) > 16 6 Order By Custid
This sectionCodeIt is a very common piece of code. Do you know the execution sequence of this statement? I believe many people do not know. If you really think you know well, please think about it for three seconds.
In mostProgramming LanguageIn, the code is executed in the order they are written, and then the SQL statement execution sequence is not the same. The SQL statement execution sequence is
From --> where --> group by --> having ---> select --> order
It can be seen that although the SELECT statement is written at the beginning, the execution sequence is not the first. I understand the execution sequence of SQL statements and can clearly explain many non-obvious errors. If you change the preceding SQL statement
1 Select Custid, Count (Custid) As Counts 2 From Orders 3 Where Custid > 12 4 Group By Custid 5 Having Counts > 16 6 Order By Counts
When you execute the preceding statement, the query analyzer reports an error. The error is 5th rows, and the column 'counts' is invalid. If you do not understand the execution sequence of the SQL statement, you may be depressed, why does it mean that the counts of the 5th row and column names is invalid, but the 6th row does not report an error? If you understand the execution sequence, it means that after the SELECT statement is executed in having and before the order by statement is executed, the counts column name is not generated in group by, but in order by statement, the column name counts has been generated. I think that's why.
In the future, I will sort out all my notes and hope you will discuss them together.