sql日記(相關子查詢，動態交叉表篇)
2024-07-21 02:11:35
供稿：網友
本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
最近重新又翻看了一下關于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 @[email protected] +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 @[email protected]+ '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 '+'"學員",'[email protected] + 'count(f_number) as '+'"考試數目"'+
'from rowdata group by f_number order by f_number'
print @sql
exec sp_executesql @sql
