Recently, I re-looked at the book about SQL Server, mainly to view knowledge about related subqueries and crosstabs in SQL. The associated subquerix and ordinary subquery differs from: the associated subquerix references the columns of external queries. The ability of this reference external query means that the associated subqueries cannot operate independently, in which external query references will make it unable to perform properly. Therefore, the execution sequence of the associated subqueries is as follows: 1. First execute an external query 2. Perform a batch query for each row of external queries, and each time the subqueries will reference the value of the external row. Use subqueries to determine the result set of external queries. For example; select t1.typefrom titles T1Group by t1.typehaving max (t1.advance)> = all (select 2 * avg (t2.advance) from titles T2 where t1pe = t2.type) This result returns the highest prepaid The type of book type exceeding the average pre-payment in the given group. In an example: Requires Return Each Number (List ID, Name, Score) ID Name (Number) Score 1 A 882 B 763 C 664 C 905 B 776 A 567 B 77 8 C 679 A 44Select * from ta where score = (SELECT MAX (SCORE) from tb where a.name = b.name) gives a ranking SQL statement Select (select count (*) 1 as dd from [Test] as a where a. [f2]
