Mysql5 實作交叉表查詢

交叉表、行列轉換和交叉查詢經典

一、什麼是交叉表

“交叉表”對象是一個網格，用來根據指定的條件傳回值。資料顯示在壓縮行和列中。這種格式易于比較資料并辨識其趨勢。它由三個元素組成：

行

列

摘要字段

“交叉表”中的行沿水準方向延伸（從一側到另一側）。在上面的示例中，“手套”(gloves) 是一行。

“交叉表”中的列沿垂直方向延伸（上下）。在上面的示例中，“美國”(usa) 是一列。

彙總字段位于行和列的交叉處。每個交叉處的值代表對既滿足行條件又滿足列條件的記錄的彙總（求和、計數等）。在上面的示例中，“手套”和“美國”交叉處的值是四，這是在美國銷售的手套的數量。

“交叉表”還可以包括若幹總計：

每行的結尾是該行的總計。在上面的例子中，該總計代表一個産品在所有國家/地區的銷售量。“手套”行結尾處的值是 8，這就是手套在所有國家/地區銷售的總數。

注意：    總計列可以出現在每一行的開頭。

每列的底部是該列的總計。在上面的例子中，該總計代表所有産品在一個國家/地區的銷售量。“美國”一列底部的值是四，這是所有産品（手套、腰帶和鞋子）在美國銷售的總數。

注意：    總計列可以出現在每一行的頂部。

“總計”(total) 列（産品總計）和“總計”(total) 行（國家/地區總計）的交叉處是總計。在上面的例子中，“總計”列和“總計”行交叉處的值是 12，這是所有産品在所有國家/地區銷售的總數。

二、行列轉換和交叉查詢：

1: 列轉為行:

eg1:

假設有張學生成績表(cj)如下

name      subject         result

張三         國文             80

張三         數學             90

張三         實體             85

李四         國文             85

李四         數學             92

李四         實體             82

相關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('張三','數學',86);

insert into cj(name,subject,result) values('張三','英語',75);

insert into cj(name,subject,result) values('李四','國文',78);

insert into cj(name,subject,result) values('李四','數學',85);

insert into cj(name,subject,result) values('李四','英語',78)

select * from cj

想變成如下的交叉表

姓名        國文        數學        實體

張三         99          90           85

李四         85          92           82

我們首先來看一下如何建立靜态的交叉表，也就是說列數固定的交叉表，這種情況其實隻要一句簡單的select查詢就可以搞定：

select name,sum(case when a.subject='國文' then result else null end) as "國文",

sum(case when a.subject='數學' then result else null end) as "數學",

sum(case when a.subject='英語' then result else null end) as "英語"

from cj a

group by name;

當要增加“總計”列："合計總分"時，如下表所示：

姓名        合計總分 國文        數學        實體

張三          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 "國文",

其中利用了case語句判斷，如果是相應的列，則取需要統計的cj數值，否則取null，然後再合計。

其中有兩個常見問題說明一下：

a、用null而不用0是有道理的，假如用0，雖然求和函數sum可以取到正确的數，但類似count函數（取記錄個數），結果就不對了，因為null不算一條記錄，而0要算，同理空字串（""）也是這樣，總之在這裡應該用null,這樣任何函數都沒問題。

b、假如在視圖的設計界面儲存以上的查詢，則會報錯“沒有輸出列”，進而無法儲存，其實隻要在查詢前面加上一段：create view viewname as ...，viewname是你準備給查詢起的名稱，...就是我們的查詢，然後運作一下，就可以生成視圖了，對于其他一些設計器不支援的文法，也可以這樣儲存。

以上查詢作用也很大，對于很多情況，比如産品銷售表中按照季度統計、按照月份統計等列頭内容固定的情況，這樣就行了，但往往大多數情況下列頭内容是不固定的，象city，使用者随時可能删除、添加一些城市，這種情況就是我們所說的動态交叉表，在sqlserver中我們可以用存儲過程來解決。下面我們補充一些知識：

相關子查詢

相關子查詢和普通子查詢差別在于：相關子查詢引用了外部查詢的列。這種引用外部查詢的能力意味着相關子查詢不能自己獨立運作，其中對于外部查詢引用會使會使其無法正常執行。是以相關子查詢的執行順序如下：

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的相關子查詢先講到這裡。

sqlserver中局部變量指派方法

有兩種:

一種: set @變量名 = 值

二種: select @變量名 = 值

第二種可以從某個表中得到資料再指派給變量

例: 從使用者資訊表中查詢中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,這是一種很優美的查詢方法.進而将基礎表中垂直的列資料改為水準方向的資料。這樣就可以替代遊标。動态的交叉表這樣就代替了傳統的遊标。

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)

寫法二：

+' from cj group by subject

具體不同的多種寫法參見本文相關連結文章中的其他例子

在access中還提供了transform來實作行列轉換

transform count(result) as number

select 姓名

from 學生成績表

group by 姓名

pivot subject;

transform 用法如下:

=========================================================

transform aggfunction

selectstatement

pivot pivotfield [in (value1[, value2[, ...]])]

transform 語句可分為以下幾個部分：

部分                 描述

aggfunction 在標明資料上運作的 sql 合計函數。

selectstatement       select 語句。

pivotfield 在查詢的結果集中建立列标題時用的字段或表達式。

value1, value2 用來建立列标題的固定值。

說明

使用交叉表查詢來摘要資料時，從指定的字段或表達式中標明值作為列标題，

這樣，可以用比標明查詢更緊湊的格式來觀察資料。

transform 是可選的，但在使用它時，要作為       sql 字元串中的第一個語句。

它出現在 select 語句（指定作為行标題的字段的）之前，還出現在 group by 子句

（指定行分組的）之前。可以有選擇地包含其它子句，例如 where 子句，它指定附

加的選擇或排序條件。也可以将子查詢當作謂詞，特别是在叉表查詢的 where 子句中。

pivotfield 傳回的值被用作查詢結果集中的列标題。

例如，在交叉表查詢中，将根據銷售圖表按銷售月份建立 12 個列。

可以限制 pivotfield 用列在可選的 in 子句中的固定值（value1, value2）來建立标題。

也可以用沒有資料存在的固定值來建立附加的列。

2. 列行轉換

暫時保留

3. 行列轉換--加合并

有表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

建立一個合并的函數

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=@id

set @str=right(@str,len(@str)-1)

return(@str)

end

go

--調用自定義函數得到結果

select distinct id,dbo.fmerg(id) from 表a

# host: localhost    database: test  

# ------------------------------------------------------  

# server version 5.0.45-community-nt-log  

#  

# table structure for table sale  

drop table if exists `sale`;  

create table `sale` (  

`id` int(10) unsigned not null auto_increment,  

`year` int(11) not null,  

`quarter` int(11) not null,  

`amount` decimal(15,2) not null,  

primary key (`id`)  

) engine=myisam auto_increment=10 default charset=latin1;  

# dumping data for table sale  

/*!40101 set names latin1 */;  

insert into `sale` values (1,2004,1,2328);  

insert into `sale` values (2,2004,2,3822);  

insert into `sale` values (3,2004,3,7071);  

insert into `sale` values (4,2004,4,8931);  

insert into `sale` values (5,2005,1,2633);  

insert into `sale` values (6,2005,2,3910);  

insert into `sale` values (7,2005,3,237193);  

insert into `sale` values (8,2005,4,567444);  

insert into `sale` values (9,2006,1,12313);  

插入資料後結果為：

id    year    quarter    amount

1    2004    1    2328.00

2    2004    2    3822.00

3    2004    3    7071.00

4    2004    4    8931.00

5    2005    1    2633.00

6    2005    2    3910.00

7    2005    3    237193.00

8    2005    4    567444.00

9    2006    1    12313.00

交叉表查詢語句：

select a.year, 1d, 2d, 3d, 4d from

(select distinct year from sale) a left join

(select year, amount 1d from sale where quarter=1 group by year) a1d on a.year = a1d.year

left join (select year, amount 2d from sale where quarter=2 group by year) a2d on a2d.year=a.year

left join (select year, amount 3d from sale where quarter=3 group by year) a3d on a3d.year=a.year

left join (select year, amount 4d from sale where quarter=4 group by year) a4d on a4d.year=a.year

該語句查詢某年的四個季度的amount，以行顯示，顯示結果：

year    1d    2d    3d    4d

2004    2328.00    3822.00    7071.00    8931.00

2005    2633.00    3910.00    237193.00    567444.00

2006    12313.00    null    null    null

實作定長列的查詢（即quarter的最大取值為4，定長為4列）.