交叉表,行列转换,交叉查询经典,分组小计合计报表,SQL,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/Render/archive/2006/12/19/596485.html

分组小计合计报表的SQL

Table1结构如下:

OrgName,StaffName, PayArea,Pay

要生成如下形式报表：

Org1,A1,100,4

Org1,A2,100,4

Org1,A3,100,4

Org1小计,XX,XX,XX

Org2,B1,100,4

....

合计, XX ,XX

SQL:

select OrgName DD,STAFFNAME,PayArea, Pay,OrgName as EE from Table1 as t1

union

select OrgName || '小计' DD,'',sum(PayArea),sum(Pay),OrgName || '_' EE from Table1 as t2 group by DEPT_ID

union

Select '合计' as DD,'',sum(PayArea),sum(Pay),'ZZZZZZZZ' as EE from Table1

order by EE

其中的OrgName||'_' 是为了取得一个比下一个不同的OraName大一些的值，以便让小计这条数据排在适当的位置，如

Org1小计要排在Org1和Org2之间，所以要选一个在数据库中字符排序号小的字符，这里以"_"表示。'ZZZZZZZZ'则

是为了把合计记录排在最后，所以要选一串在数据库中字符排序最大的字符构成的串，这里只是用'Z'来表示。

用ee 排序，但合计中

'ZZZZZZZZ' EE ，如果OrgName是汉字的话，那么排序后，合计将会变为第一行的，英文字母总是显示在汉字前面，

这样就达不到合计显示在最后一行的目的了，如果将合计中'ZZZZZZZZ' EE 变为'做做做做做做做做' EE 这样就能使

合计排在最后一行，因为'做'字是字典中最后一个字

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

交叉表、行列转换和交叉查询经典

Access 静态叉表行列转换统计语句

SELECT a.客户号,

sum(iif(a.期初五级=1,a.期初余额,null)) as "期初正常",

sum(iif(a.期初五级=2,a.期初余额,null)) as "期初关注",

sum(iif(a.期初五级=3,a.期初余额,null)) as "期初次级",

sum(iif(a.期初五级=4,a.期初余额,null)) as "期初可疑",

sum(iif(a.期初五级=5,a.期初余额,null)) as "期初损失",

sum(a.期初余额) as 期初总计,

sum(iif(a.期未五级=1,a.期未余额,null)) as "期未正常",

sum(iif(a.期未五级=2,a.期未余额,null)) as "期未关注",

sum(iif(a.期未五级=3,a.期未余额,null)) as "期未次级",

sum(iif(a.期未五级=4,a.期未余额,null)) as "期未可疑",

sum(iif(a.期未五级=5,a.期未余额,null)) as "期未损失",

sum(a.期未余额) as 期未总计,

FROM [work-1] as a

GROUP BY a.客户号;

/content/10/0609/23/314324_32230468.shtml

一、什么是交叉表

“交叉表”对象是一个网格，用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别

其趋势。它由三个元素组成：

行

列

摘要字段

“交叉表”中的行沿水平方向延伸（从一侧到另一侧）。在上面的示例中，“手套”(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如下：