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 (
