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 "语文",
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;

其中利用了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)

写法二：

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)

具体不同的多种写法参见本文相关链接文章中的其他例子

在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

Java代码  

1. # Host: localhost    Database: test  
2. # ------------------------------------------------------  
3. # Server version 5.0.45-community-nt-log  
4.   
5. #  
6. # Table structure for table sale  
7. #  
8.   
9. DROP TABLE IF EXISTS `sale`;  
10. CREATE TABLE `sale` (  
11. `id` int(10) unsigned NOT NULL auto_increment,  
12. `year` int(11) NOT NULL,  
13. `quarter` int(11) NOT NULL,  
14. `amount` decimal(15,2) NOT NULL,  
15. PRIMARY KEY (`id`)  
16. ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;  
17.   
18. #  
19. # Dumping data for table sale  
20. #  
21.   
22. /*!40101 SET NAMES latin1 */;  
23.   
24. INSERT INTO `sale` VALUES (1,2004,1,2328);  
25. INSERT INTO `sale` VALUES (2,2004,2,3822);  
26. INSERT INTO `sale` VALUES (3,2004,3,7071);  
27. INSERT INTO `sale` VALUES (4,2004,4,8931);  
28. INSERT INTO `sale` VALUES (5,2005,1,2633);  
29. INSERT INTO `sale` VALUES (6,2005,2,3910);  
30. INSERT INTO `sale` VALUES (7,2005,3,237193);  
31. INSERT INTO `sale` VALUES (8,2005,4,567444);  
32. 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列）.