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)
Place a ranking SQL statement Select (select count (*) 1 as dd from [test] as a where a. [F2] Here's the concept of the crosstab that the crosstab will first click on the Select variable recursion Select variable to use the SELECT statement and subquery to put a variable with its own splicing. Equal SELECT @ var = @ var d.column from table1 a change the column data perpendicular to the base table to the horizontal direction. This can replace the cursor. Below is a comparison of dynamic crosstabs and static crosstabs, and dynamic crosstabs will replace traditional cursors. Cross-table Method 1Select F_Number AS 'Student', SUM (Case F_Subject When 'A01' TENF_NUM END) AS 'A01', SUM (Case F_Subject When 'A02' TENF_NUM END) AS 'A02', SUM (Case F_Subject When ' A03 'TENF_NUM END) AS' A03 ', SUM (Case F_NUBJECT WHEN' A04 'THEN F_NUM END) AS' A04 ', SUM (Case F_Subject When' A05 'TENF_NUM END) AS' A05 ', SUM (Case F_Subject When 'A06' TENF_NUM END AS 'A06', SUM (Case F_Subject When 'A07' TENF_NUM END) AS 'A07', SUM (Case F_Subject When 'A08' TENF_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 2declare @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 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) ASET @ SQL = 'SELECT F_NUMBER AS' '"Student",' @ SQL 'Count (f_number) AS' '"Test Number"' 'from rowdata group by f_number order by f_number'print @SQLEXEC SP_EXECUTESQL @SQL
