最近重新又翻看了一下关于sqlserver的书籍，主要查看了一下关于sql中的相关子查询和交叉表方面的知识。 相关子查询和普通子查询区别在于：相关子查询引用了外部查询的列。 这种引用外部查询的能力意味着相关子查询不能自己独立运行，其中对于外部查询引用会使会使其无法正常执行。因此相关子查询的执行顺序如下： 1.首先执行一遍外部查询 2.对于外部查询的每一行分别执行一遍子查询，而且每次执行子查询时候都会引用外部的当前行的值。 使用子查询的结果来确定外部查询的结果集。 举个例子； 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) 这个结果返回最高预付款超过给定组中平均预付款两倍的书籍类型。 在举个例子： 要求返回每一个编号的最大值(列出id,name,score) ID Name（编号） 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)
再给一个排位的sql语句 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]; 好了关于sql的相关子查询先讲到这里。
下面说一下交叉表的概念 说到交叉表先提一下递归的select变量 递归的select变量可以使用select语句和子查询将一个变量与其自身拼接起来。 举一个例子 select @var=@var d.column from table1 a 从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。 下面就是动态交叉表和静态的交叉表的一个比较，动态的交叉表这样就代替了传统的游标。 交叉表 方法1 select f_nUMBER as '学员', 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
方法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 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 ) a set @sql='select f_nUMBER as ' '"学员",' @sql 'count(F_Number) as ' '"考试数目"' 'from rowdata group by F_Number order by F_Number' print @sql exec sp_executesql @sql