SQL diary (related subqueries, dynamic cross tabulation)

I recently reviewed the SQL Server books again, mainly looking at SQL-related subqueries and cross-Table knowledge.
The difference between a subquery and a common subquery is that a subquery references a column in an external query.
This ability to reference external queries means that the related subqueries cannot run independently, and external query references will make them unable to run normally. Therefore, the subquery execution sequence is as follows:
1. Execute an external query first.
2. Execute the subquery once for each row of the external query, and the value of the current external row will be referenced each time the subquery is executed.
Use the results of the subquery to determine the result set of the external query.
For example;
Select t1.type
From titles T1
Group by t1.type
Having max (t1.advance)> = all
(Select 2 * AVG (t2.advance)
From titles T2
Where t1.type = t2.type)
This result returns the type of books whose maximum prepayment is twice the average Prepayment in the given group.
For example:
Returns the maximum value of each number (list ID, name, score)
ID name (number) score (score)
1 A 88
2 B 76
3 C 66
4 C 90
5 B 77
6 A 56
7 B 77
8 c 67
9 A 44
Select * from t a where score =
(Select max (score) from t B where a. Name = B. Name)

Give an SQL statement in the ranking.
Select (
Select count (*) + 1 as dd
From [test] As a where a. [F2] <B. [F2]) as ord, B. [F1], B. [F2]
From [test] as B
Order by B. [F2];
Well, here we will talk about SQL-related subqueries first.

The following describes the concept of a cross tabulation.
Speaking of cross-tabulation, we should first mention recursive select variables.
Recursive select variables can be spliced with themselves using select statements and subqueries.
For example
Select @ Var = @ var + D. column from Table1
In this way, the vertical column data in the basic table is changed to the horizontal data. In this way, the cursor can be replaced.
The following is a comparison between a dynamic cross tabulation and a static cross tabulation. A dynamic cross tabulation replaces a traditional cursor.
Cross tabulation
Method 1
Select f_number as 'trainee ',
Sum (Case f_subject when 'a01' then f_num end) as 'a01 ',
Sum (Case f_subject when 'a02' then f_num end) as 'a02 ',
Sum (Case f_subject when 'a03' then f_num end) as 'a03 ',
Sum (Case f_subject when 'a04 'Then f_num end) as 'a04 ',
Sum (Case f_subject when 'a05 'Then f_num end) as 'a05 ',
Sum (Case f_subject when 'a06' then f_num end) as 'a06 ',
Sum (Case f_subject when 'a07' then f_num end) as 'a07 ',
Sum (Case f_subject when 'a08' then f_num end) as 'a08 ',
Sum (Case f_subject when 'a09' then f_num end) as 'a09'
From rowdata group by f_number order by f_number

Method 2
Declare @ SQL nvarchar (2000)
Set @ SQL =''
Select @ SQL = @ SQL + 'sum (Case f_subject when ''' + A. f_subject + ''' then f_num else 0 end)'
+ A. f_name + ','
From (select distinct Top 100 percent f_subject, f_name from rowdata B join subject_name C on B. f_subject = C. f_number order by f_subject)
Set @ SQL = 'select f_number as '+' "student", '+ @ SQL + 'count (f_number) as' + '"Exam count"' +
'From rowdata group by f_number order by f_number'
Print @ SQL
Exec sp_executesql @ SQL