What can SQL CTE help me do?

The company environment where programmers are located is not the same, so programmers have a big difference in IT skills. For example, in some companies, programmers only focus on business logic processing or page UI. Of course, I am a programmer related to web applications. Because it is possible that all the underlying data is provided to you by other colleagues, this will result in the unimaginable and ugly SQL statements when you need to write your own database scripts for query, however, if we pay attention to it, we will know that SQL 2005's CTE can solve this situation in a large number of programs. This is because programmers are more aware of themselves.

When we mention the SQL cte, it is likely that the first reaction is its powerful recursive query, but I do not want to talk about this knowledge here.

Let's take a look at its definition.: Specifies a result set with a temporary name. These result sets are called common table expressions (CTE ). This expression is derived from a simple query and is defined within the execution range of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in the create view statement as part of the SELECT Definition Statement of the statement. Common table expressions can include references to themselves. This expression is called a recursive common table expression.

CTE classification:1: recursive CTE; 2: Non-recursive CTE.

I will summarize the second one today. The rational use of CTE mentioned above can avoid ugly SQL. First, I will post an SQL statement for nested queries: I want to see such an SQL statement, it is difficult to figure out its true intention in a short time.

Code

SELECT r. room_type AS room_type,
A .*
FROM (SELECT f. id,
End_date, start_date,
(F. room_num-ISNULL (select sum (a. free_room_used)
From c
WHERE a. free_room_id = f. id
Group by a. free_room_id
), 0) AS free_num
From a f
WHERE (f. deleted <> 1
OR f. deleted IS NULL
)
AND '2014-3-16 'BETWEEN f. start_date AND f. end_date
)
Inner join B r ON r. room_type_id = a. room_type_id
AND (r. notes = 'all'
OR r. notes = '000000'
)
WHERE a. free_num> 0
Order by a. end_date,
A. start_date

Let's take a look at the improved CTE statement: we can see that there is no nesting, and two CTE are used to replace subqueries respectively.

 

Code

BEGIN
WITH freoom
AS (select sum (a. free_room_used) AS used,
Free_room_id
From c
Inner join B f ON a. free_room_id = f. id
Group by a. free_room_id
),
Freehotel
AS (SELECT r. room_type AS room_type,
F. id,
Start_date, end_date,
(F. room_num-ISNULL (fr. used, 0) AS free_num
From a f
Left join freoom fr ON f. id = fr. free_room_id
Inner join B r ON r. room_type_id = f. room_type_id
AND (r. notes = 'all'
OR r. notes = '000000'
)
WHERE (f. deleted <> 1
OR f. deleted IS NULL
)

AND '2014-3-16 'BETWEEN f. start_date AND f. end_date
)
SELECT *
FROM freehotel
WHERE free_num> 0
Order by end_date,
Start_date

END

 

First:Avoid querying fields as much as possible, such as taking free_num in the previous example and directly nesting the query. This method is uncomfortable.
Improvement: Use a CET expression to represent this subquery. See the freoom above.

 

Second:In the above example, because the free_num field is not actually a field in the table, it is calculated and waited for, so to use it as the where condition, you have to nest it again.

Improvement: we also use CTE to express the sub-table above. See freehotel above.

 

Third:Directly query freehotel, and add the query conditions and sorting statements.

 

Summary:Through the above three steps, we can clearly divide the nested two-layer query above, and then analyze the data after it is reduced to zero. The idea is much clearer.

Note: This is intended for non-recursive CTE.

 

1: After CTE, you must follow a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all of the CTE columns. You can also specify CTE as part of the SELECT Definition Statement in the create view statement. Otherwise, the CTE will become invalid.

2: Multiple WITH clauses cannot be specified in a CTE.

 

3: The CTE can reference itself, or it can reference the pre-defined CTE in the same WITH clause. Forward references are not allowed.

As in the example above, the same with is defined in advance, and the freehotel later uses the previous freeroom.

4: the following clause cannot be used in CTE_query_definition:

COMPUTE or COMPUTE

Order by (unless TOP clause is specified)
INTO

OPTION clause with query prompt
FOR XML
FOR BROWSE

5: if you use the CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon.

Another way of saying this is to include the entire cte definition and the final operations in the begin and end begin blocks, so as to avoid forming a batch process with other statements.

 

Relationship between CTE and table variables:Non-recursive CTE can replace table variables in a large program. Of course, the only difference is that table variables can be valid throughout the Query Process, and CTE needs to be called immediately after, but the advantage is that it saves overhead because table variables increase the overhead of system I/O. In addition, CTE does not need to define the table structure before use, just like the table variables, and has the advantages of high maintainability.

 

 

Author: Jiang min
Source: http://www.cnblogs.com/aspnet2008/