交叉表、行列轉(zhuǎn)換和交叉查詢經(jīng)典
一、什么是交叉表
“交叉表”對象是一個網(wǎng)格，用來根據(jù)指定的條件返回值。數(shù)據(jù)顯示在壓縮行和列中。這種格式易于比較數(shù)據(jù)并辨別其趨勢。它由三個元素組成：
行
列
摘要字段
“交叉表”中的行沿水平方向延伸（從一側(cè)到另一側(cè)）。在上面的示例中，“手套”(Gloves) 是一行。
“交叉表”中的列沿垂直方向延伸（上下）。在上面的示例中，“美國”(USA) 是一列。
匯總字段位于行和列的交叉處。每個交叉處的值代表對既滿足行條件又滿足列條件的記錄的匯總（求和、計數(shù)等）。在上面的示例中，“手套”和“美國”交叉處的值是四，這是在美國銷售的手套的數(shù)量。
“交叉表”還可以包括若干總計：
每行的結(jié)尾是該行的總計。在上面的例子中，該總計代表一個產(chǎn)品在所有國家/地區(qū)的銷售量。“手套”行結(jié)尾處的值是 8，這就是手套在所有國家/地區(qū)銷售的總數(shù)。
注意： 總計列可以出現(xiàn)在每一行的開頭。
每列的底部是該列的總計。在上面的例子中，該總計代表所有產(chǎn)品在一個國家/地區(qū)的銷售量。“美國”一列底部的值是四，這是所有產(chǎn)品（手套、腰帶和鞋子）在美國銷售的總數(shù)。
注意： 總計列可以出現(xiàn)在每一行的頂部。
“總計”(Total) 列（產(chǎn)品總計）和“總計”(Total) 行（國家/地區(qū)總計）的交叉處是總計。在上面的例子中，“總計”列和“總計”行交叉處的值是 12，這是所有產(chǎn)品在所有國家/地區(qū)銷售的總數(shù)。
二、行列轉(zhuǎn)換和交叉查詢：
1: 列轉(zhuǎn)為行:
eg1:
假設(shè)有張學(xué)生成績表(CJ)如下
name subject result
張三 語文 80
張三 數(shù)學(xué) 90
張三 物理 85
李四 語文 85
李四 數(shù)學(xué) 92
李四 物理 82
相關(guān)sql語句：
Create table CJ(name char(10),subject char(10),result int);
insert into CJ(name,subject,result) values('張三','語文',99);
insert into CJ(name,subject,result) values('張三','數(shù)學(xué)',86);
insert into CJ(name,subject,result) values('張三','英語',75);
insert into CJ(name,subject,result) values('李四','語文',78);
insert into CJ(name,subject,result) values('李四','數(shù)學(xué)',85);
insert into CJ(name,subject,result) values('李四','英語',78)
select * from CJ
想變成如下的交叉表
姓名 語文 數(shù)學(xué) 物理
張三 99 90 85
李四 85 92 82
我們首先來看一下如何建立靜態(tài)的交叉表，也就是說列數(shù)固定的交叉表，這種情況其實(shí)只要一句簡單的Select查詢就可以搞定：
select name,sum(case when a.subject='語文' then result else null end) as "語文",
sum(case when a.subject='數(shù)學(xué)' then result else null end) as "數(shù)學(xué)",
sum(case when a.subject='英語' then result else null end) as "英語"
from CJ a
group by name;
當(dāng)要增加“總計”列："合計總分"時，如下表所示：
姓名 合計總分 語文 數(shù)學(xué) 物理
張三 260 99 90 85
李四 241 85 92 82
只需增加sum(a.result) as "合計總分"，sql如下：
select name,sum(a.result) as "合計總分",
sum(case when a.subject='語文' then result else null end) as "語文",
sum(case when a.subject='數(shù)學(xué)' then result else null end) as "數(shù)學(xué)",
sum(case when a.subject='英語' then result else null end) as "英語"
from CJ a
group by name;
其中利用了CASE語句判斷，如果是相應(yīng)的列，則取需要統(tǒng)計的cj數(shù)值，否則取NULL，然后再合計。
其中有兩個常見問題說明一下：
a、用NULL而不用0是有道理的，假如用0，雖然求和函數(shù)SUM可以取到正確的數(shù)，但類似COUNT函數(shù)（取記錄個數(shù)），結(jié)果就不對了，因?yàn)镹ull不算一條記錄，而0要算，同理空字串（""）也是這樣，總之在這里應(yīng)該用NULL,這樣任何函數(shù)都沒問題。
b、假如在視圖的設(shè)計界面保存以上的查詢，則會報錯“沒有輸出列”，從而無法保存，其實(shí)只要在查詢前面加上一段：Create View ViewName AS ...，ViewName是你準(zhǔn)備給查詢起的名稱，...就是我們的查詢，然后運(yùn)行一下，就可以生成視圖了，對于其他一些設(shè)計器不支持的語法，也可以這樣保存。
以上查詢作用也很大，對于很多情況，比如產(chǎn)品銷售表中按照季度統(tǒng)計、按照月份統(tǒng)計等列頭內(nèi)容固定的情況，這樣就行了，但往往大多數(shù)情況下列頭內(nèi)容是不固定的，象City，用戶隨時可能刪除、添加一些城市，這種情況就是我們所說的動態(tài)交叉表，在SQLServer中我們可以用存儲過程來解決。下面我們補(bǔ)充一些知識：
相關(guān)子查詢
相關(guān)子查詢和普通子查詢區(qū)別在于：相關(guān)子查詢引用了外部查詢的列。這種引用外部查詢的能力意味著相關(guān)子查詢不能自己獨(dú)立運(yùn)行，其中對于外部查詢引用會使會使其無法正常執(zhí)行。因此相關(guān)子查詢的執(zhí)行順序如下：
1.首先執(zhí)行一遍外部查詢
2.對于外部查詢的每一行分別執(zhí)行一遍子查詢，而且每次執(zhí)行子查詢時候都會引用外部的當(dāng)前行的值。使用子查詢的結(jié)果來確定外部查詢的結(jié)果集。
舉個例子；
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)
這個結(jié)果返回最高預(yù)付款超過給定組中平均預(yù)付款兩倍的書籍類型。
再舉個例子：
要求返回每一個編號的最大值(列出id,name,score)
ID Name（編號） Score（分?jǐn)?shù)）
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];
好了關(guān)于sql的相關(guān)子查詢先講到這里。
SQLServer中局部變量賦值方法
有兩種:
一種: set @變量名 = 值
二種: select @變量名 = 值
第二種可以從某個表中得到數(shù)據(jù)再賦值給變量
例: 從用戶信息表中查詢中cid為 20 的用戶姓名將他賦值給變量 name
declare @name varchar(10) --用戶名
select @name=userName from userInfo where cid = 20
print 'cid為20的用戶姓名:' + @name
遞歸的select變量
遞歸的select變量是指使用select語句和子查詢將一個變量與其自身拼接起來。語法形式如下：select @variable = @variable + table.column from table---見《sql server2000寶典》：P354,這是一種很優(yōu)美的查詢方法.從而將基礎(chǔ)表中垂直的列數(shù)據(jù)改為水平方向的數(shù)據(jù)。這樣就可以替代游標(biāo)。動態(tài)的交叉表這樣就代替了傳統(tǒng)的游標(biāo)。
SQL語句解決方法：
寫法一：
declare @sql varchar(4000)
set @sql = 'select name'
select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
from (select distinct subject from CJ) as a
select @sql = @sql+' from CJ group by name'
exec(@sql)
寫法二：
declare @sql varchar(4000)
set @sql = 'select name'
select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
+' from CJ group by subject
select @sql = @sql+' from CJ group by name'
exec(@sql)
具體不同的多種寫法參見本文相關(guān)鏈接文章中的其他例子
在Access中還提供了TransForm來實(shí)現(xiàn)行列轉(zhuǎn)換
TRANSFORM count(Result) AS number
SELECT 姓名
FROM 學(xué)生成績表
GROUP BY 姓名
PIVOT Subject;
TransForm 用法如下:
=========================================================
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
TRANSFORM 語句可分為以下幾個部分：
部分 描述
aggfunction 在選定數(shù)據(jù)上運(yùn)作的 SQL 合計函數(shù)。
selectstatement SELECT 語句。
pivotfield 在查詢的結(jié)果集中創(chuàng)建列標(biāo)題時用的字段或表達(dá)式。
value1, value2 用來創(chuàng)建列標(biāo)題的固定值。
說明
使用交叉表查詢來摘要數(shù)據(jù)時，從指定的字段或表達(dá)式中選定值作為列標(biāo)題，
這樣，可以用比選定查詢更緊湊的格式來觀察數(shù)據(jù)。
TRANSFORM 是可選的，但在使用它時，要作為 SQL 字符串中的第一個語句。
它出現(xiàn)在 SELECT 語句（指定作為行標(biāo)題的字段的）之前，還出現(xiàn)在 GROUP BY 子句
（指定行分組的）之前?？梢杂羞x擇地包含其它子句，例如 WHERE 子句，它指定附
加的選擇或排序條件。也可以將子查詢當(dāng)作謂詞，特別是在叉表查詢的 WHERE 子句中。
pivotfield 返回的值被用作查詢結(jié)果集中的列標(biāo)題。
例如，在交叉表查詢中，將根據(jù)銷售圖表按銷售月份創(chuàng)建 12 個列。
可以限制 pivotfield 用列在可選的 IN 子句中的固定值（value1, value2）來創(chuàng)建標(biāo)題。
也可以用沒有數(shù)據(jù)存在的固定值來創(chuàng)建附加的列。
2. 列行轉(zhuǎn)換
暫時保留
3. 行列轉(zhuǎn)換--加合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
創(chuàng)建一個合并的函數(shù)
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where
id=@idset @str=right(@str,len(@str)-1)
return(@str)
End
go
--調(diào)用自定義函數(shù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表A
相關(guān)鏈接：
