In a large number of programming languages, code is processed in coded order, but in the SQL language, the first processed clause is the FROM clause, although the SELECT statement appears first, but is almost always finally processed.
Each step produces a virtual table that is used as input to the next step. These virtual tables are not available to the caller (client application or external query). Only the table that is generated in the last step is returned to the caller. If a clause is not specified in the query, the corresponding step is skipped. The following is a brief description of each logical step applied to SQL Server 2000 and SQL Server 2005.
Copy Code code as follows:
(8) SELECT (9) DISTINCT (one) <top num> <select list>
(1) from [left_table]
(3) <join_type> join <right_table>
(2) on <join_condition>
(4) WHERE <where_condition>
(5) GROUP by <group_by_list>
(6) With <cube | Rollup>
(7) Having (10) ORDER BY <order_by_list>
Introduction to the logical query processing phase
1.FROM: Performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause to generate a virtual table VT1
2.ON: Apply on filter to VT1. Only those rows that make <join_condition> true are inserted into the VT2.
3.OUTER (Join): If a OUTER join is specified (relative to the cross join or (INNER join), the reserved table (preserved table: Left outer join marks the left table as a reserved table, and the right outer join marks the right table as a reserved table, A full outer join that has two tables marked as a reserved table does not find matching rows added to VT2 as an outer row. Generate VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the resulting table and the next table for the last join until all the tables are processed.
4.WHERE: Apply a WHERE filter to VT3. Only rows that make <where_condition> true are inserted into the VT4.
5.GROUP by: Groups The rows in the VT4 by the list of columns in the GROUP BY clause, generating VT5.
6.cube| ROLLUP: Insert the Super Group (suppergroups) into the VT5 and generate the VT6.
7.HAVING: A having filter is applied to the VT6. Only groups that make 8.SELECT: Process A SELECT list, handle various accumulation functions, and produce VT8.
9.DISTINCT: Remove duplicate rows from VT8, resulting in VT9.
10.ORDER by: The row in the VT9 is sorted by the column list in the ORDER BY clause, and the cursor (VC10) is generated.
11.TOP: Selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.
Note: Step 10 returns the cursor VC10 by sorting the rows returned by the column list in the ORDER BY clause. This step is the first and only step you can use the column aliases in the select list. This step differs from the other steps in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. A collection does not sort its rows in advance, it is just a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.
Because this step does not return a table (but instead returns a cursor), a query that uses an ORDER BY clause cannot be used as a table expression. Table expressions include: views, inline table-valued functions, subqueries, derived tables, and shared expressions. Its results must be returned to the client application that expects the physical record to be recorded.
For example, the following derived table query is invalid and produces an error:
Copy Code code as follows:
SELECT *
From (select Orderid,customerid to Orders by OrderID)
As D
The following view also generates an error
Copy Code code as follows:
CREATE VIEW My_view
As
SELECT *
From Orders
ORDER BY OrderID
error message:MSG 1033, level, State 1, Procedure My_viewasselect, line 2The ORDER BY clause be invalid in views, inline functions, D erived tables, Subqueries,and common table expressions, unless top or for XML is also specified. In SQL, a query with an ORDER BY clause is not allowed in a table expression, but there is an exception in T-SQL (apply the top option).
So remember, do not assume any particular order for the rows in the table. In other words, do not specify an ORDER BY clause unless you are certain that you want to do so. Sorting is a cost-requiring SQL Server to perform an ordered index scan or to use a sort run character.
recommend a section of SQL code: row and column transpose
Copy Code code as follows:
/* Question: Suppose there is a student score table (TB) as follows:
Name course score
John Language
John Math,
John Physics 93
Dick
Dick Math
Dick Physical
wants to become (get the following result):
Name language math Physics
----------------
Dick 74&NBSP;&NB Sp 84
John 74 83
-------------------
*/
CREATE TABLE TB (name Varch AR (10), course varchar (10), score int)
INSERT into TB values (' John ', ' language ',)
INSERT into TB values (' John ', ' math ', 83) Br>insert into TB values (' John ', ' Physics ', ') '
INSERT into TB values (' Dick ', ' languages ',)
INSERT into TB values (' Dick ', ' Math ', ' $ '
INSERT into TB values (' dick ', ' physical ', ')
Go
--sql SERVER 2000 static SQL, refers to the course only language, mathematics, physics, the three courses. (in the same below)
Select name as name,
MAX (case course when ' language ' then score else 0 end) language,
MAX (case course when ' math ' then score else 0 end) Math,
MAX (case course when ' physical ' then fraction else 0 end) physical
from TB
Group by name
