交叉表查询语法比较

交叉表查询语法比较 使用交叉表查询可以让汇总数据更容易阅读，交叉表查询计算总和、平均值或其他聚合函数 ，然后按照两组值对结果进行分组：一组值垂直分布在数据表的一侧，而另一组值水平分布在数据表的顶端。在运行交叉表查询时，结果显示在一个数据表中，该数据表的结构不同于其他类型的数据表，与显示相同数据的简单选择查询相比，交叉表查询的结构让数据更易于阅读。 SqlServer2005中对交叉表查询进行了语法上的简化，但是交叉表查询却不是SqlServer2005独有的，事实上Pivot语法源自Access数据库。而在没有这种新语法之前，SqlServer2000中通常使用Case语法来实现交叉表查询。 而这2种方式都不是标准的Sql语句， 其实还有另外一种交叉表查询方式，利用复杂的自我连接可以形成一个交叉表结果（但是可读性将非常差！）本文将会构造一个比较典型的简单例子，分别用几种方式实现，可以简单对比一下。 假设有学生分数表PupleMark如下(这表只是为了说明问题,这并不是一个好的设计,也不符合第3范式标准)： Name Grade Project Mark 甲 1 数学 80 甲 1 语文 70 甲 1 地理 70 甲 1 英语 100 乙 5 语文 75 丙 4 数学 95 乙 5 数学 50 乙 5 英语 87 ...... ...... ....... ....... 假设我们需要查询出一个汇总表,看见3-6年级的数学、英语、语文（其他科目不需要，一二年级不需要）的平均分,大约如下： 语文 数学 英语 3 4 5 6 先来看看Access下的语法: TRANSFORM Avg(Mark) SELECT Grade FROM PupleMark WHERE Grade > 2 GROUP BY Grade ORDER BY Grade PIVOT Project IN ('语文','数学','英语') 接下来是SqlServer2000中常用的case语法: SELECT Grade, Avg(Case Project WHEN '语文' THEN Mark ELSE NULL END), Avg(Case Project WHEN '数学' THEN Mark ELSE NULL END), Avg(Case Project WHEN '英语' THEN Mark ELSE NULL END) FROM PupleMark WHERE Grade > 2 GROUP BY Grade ORDER BY Grade 然后是SqlServer2005中新增加的交叉表语法: SELECT Grade,语文,数学,英语 FROM (SELECT Grade, Mark, Project FROM PupleMark ) A PIVOT (Avg(Mark) FOR Project IN (语文,数学,英语)) WHERE Grade > 2 ORDER BY Grade 最后是可读性很差的标准连接语法： SELECT A.Grade, A.数学, B.语文,C.英语 FROM (SELECT Grade,Avg(Mark) AS 数学 FROM PupleMark WHERE Project='数学' GROUP BY Grade) A INNER JOIN (SELECT Grade,Avg(Mark) AS 语文 FROM PupleMark WHERE Project='语文' GROUP BY Grade) B ON A.Grade = B.Grade INNER JOIN (SELECT Grade,Avg(Mark) AS 英语 FROM PupleMark WHERE Project='英语' GROUP BY Grade) C ON B.Grade = C.Grade WHERE A.Grade > 2 ORDER BY Grade