SQL Diary (correlated subquery, dynamic crosstab)

Dynamic
Recently looked back at the SQL Server books, mainly on the SQL related subqueries and crosstab knowledge.
The difference between a correlated subquery and a normal subquery is that the related subquery references the columns of the outer query.
This ability to refer to an external query means that the dependent subquery cannot run independently of itself, and that the external query reference makes it impossible to perform properly. Therefore, the execution order of the correlated subqueries is as follows:
1. Perform an external query first
2. Execute the subquery for each row of the external query separately, and reference the value of the current row outside each time the subquery is executed.
Use the results of a subquery to determine the result set for an external query.
Give an 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 book with the highest prepayment exceeding twice times the average prepayment in a given group.
For example:
Requires the maximum value of each number to be returned (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)

And give a ranked SQL statement
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];
All right, here's the related subquery about SQL first.

Let's say the concept of a cross table
When it comes to the cross table, first mention the recursive select variable.
A recursive select variable can concatenate a variable with itself using a SELECT statement and a subquery.
Give an example
Select @var = @var +d.column from table1 a
This changes the vertical column data in the underlying table to horizontally oriented data. This allows you to override the cursor.
The following is a comparison of the dynamic crosstab and the static crosstab, which replaces the traditional cursors with the dynamic crosstab.
Cross table
Method 1
Select F_number as ' student ',
SUM (case f_subject the ' A01 ' then F_num end) as ' A01 ',
SUM (case f_subject the ' A02 ' then F_num end) as ' A02 ',
SUM (case f_subject the ' A03 ' then F_num end) as ' A03 ',
SUM (case f_subject the ' A04 ' then F_num end) as ' A04 ',
SUM (case f_subject the ' A05 ' then F_num end) as ' A05 ',
SUM (case f_subject the ' A06 ' then F_num end) as ' A06 ',
SUM (case f_subject the ' A07 ' then F_num end) as ' A07 ',
SUM (case f_subject the ' A08 ' then F_num end) as ' A08 ',
SUM (case f_subject the ' 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) as '
+a.f_name+ ', '
From (select distinct the percent f_subject,f_name from RowData b JOIN subject_name C on B.f_subject=c.f_number order by F_subject) A
Set @sql = ' Select F_number as ' + ' student ', ' + @sql + ' count (f_number) as ' + ' "exam number" ' +
' From RowData Group by F_number ORDER by F_number '
Print @sql
EXEC sp_executesql @sql