|
|
。 TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
。 TRANSFORM 语句包含以下部分：
• 部分说明aggfunction操作所选数据的 SQL 聚合函数。selectstatementSELECT 语句。pivotfield希望用于创建查询结果集中列标题的字段或表达式。value1，value2用于创建列标题的固定值。
• 说明
• 使用交叉表查询汇总数据时，将从作为列标题的指定字段或表达式中选择值，以便能够以一种比使用选择查询更紧凑的方式来查看数据。
• TRANSFORM 是可选的，但被包括时则是 SQL 字符串中的第一个语句。它在 SELECT 语句之前以指定作为行标题的字段，在 GROUP BY 子句之前以指定行分组方法。您也可以包含其他子句（如 WHERE），以指定其他选择或排序条件。还可以在交叉表查询中使用子查询作为谓词，特别是在 WHERE 子句中。
• pivotfield 中返回的值作为查询结果集中的列标题。例如，在交叉表查询中如果依据月销售量来透视销售数据，将会创建 12 个列。可以约束 pivotfield 以便从可选 IN 子句中所列出的固定值（value1，value2）内选择标题。也可以包含固定值用于没有数据来创建其他列的情况
该语句可以用于OLE EB 窗体，也可以用于VBA中。
我们来用范例讲解一下这个SQL语句
http://club.excelhome.net/viewth ... highlight=Transform
我的SQL语句如下：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col) x
left join
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
group by CCY
PIVOT col
今天我出差回来了，继续这个话题。
TRANSFORM 改变的意思
PIVOT 计算机中就是透视表的意思
aggfunction SQL的 聚合函数，既然是聚合函数，那么就要用到group by
数据透视表说白了就是实现聚合功能，但没SQL的强，一般就是实现求和和求个数的功能，
如果你将所有行字段和列字段都用用SQL的group by来求和或求个数，然后用数据透视表的求和显示，数据源和结果都是唯一的。如果是原始数据源，用数据透视表实现的就是group by 的功能，
TRANSFORM 。。。PIVOT SQL语句有一些限制：
1、只有一个聚合函数，
2、只有一个字段的不重复记录变成列字段。
很容易明白，将数据处理后的结果为一个2维的新表，保留数据透视表的数据结构，但显示的结果更像一个新的结果表
3、Select 后跟的行字段可以多个，所有有人用连接符来求行字段就不可取了，但别忘了所取的行字必须出现在Group by 后面的行字段。
4、[IN (value1[, value2[, ...]])]，有时取为列的字段有很多项目类别，甚至有可能超出256，这样结果可能报错，同时我们往往关系的是其中几个项目类，其他的不关心，这样我们就可以选择我们要的项目作为新表的列，我们只要将记录中要的字段写入括号就可以了。
如果我们只有后面有M字符的记录
语句可以写成为：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col) x
left join
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
group by CCY
PIVOT col in ('1M','2M','3M','4M','5M','6M','9M')
等效SQL语句有：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col in ('1M','2M','3M','4M','5M','6M','9M')
group by CCY
PIVOT col
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col like '%M'
group by CCY
PIVOT col
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col ='1M' or col ='2M' or col ='3M' or col ='4M' or col ='5M' or col ='6M' or col ='9M'
group by CCY
PIVOT col
共4种方式写的SQL语句
第一次采用1-4的SQL语句测试速度，第二次采用4-1的SQL语句测试速度，速度差不多，两次统计如下：
1 2 3 4
2.483 2.486 2.505 2.523
4 3 2 1
2.838 2.844 2.853 2.856
数据量少可能不能说明什么，真正用于数据处理的时间同打开文件等等相比可能较小，这样速度就不是特别重要的因素。个人认为第3个语句比较简洁。
附件提供有编程代码，有兴趣的朋友可以测试一下，我将数据输出屏蔽了，要实现在result工作表中显示结果，将后面屏蔽部分恢复过来就是。
针对该SQL语句，给大家相信分析一下。
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(
select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join
(
select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col like '%M'
group by CCY
PIVOT col
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
提取后面要用的记录中的将要用作列字段的信息
select LEFT(RIC,3) AS CCY
from [Data$]
提取CCY所有字段
select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(
select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col
将CCY和col组合起来的记录，记录数会是两个数相乘。肯定覆盖所有CCY和col组合在一起的记录。
不用这种方法。既含有CCY又含有col字段的记录就不一定都存在。
select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
将每个都要相加的平均值并命名为SPOT的记录提取去来。
select x.CCY,x.col,y.data
from
(
select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(
select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col
) x
left join
(
select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
) y
on x.CCY=y.CCY
前面CCY和col组合的所有记录添加字段用left join 方式被join的数据填充。
这样就给每个CCY和col字段添加了一个都要相加的数据。
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
将所有拆分字段形成的记录做数据处理，求平均再除以10000.
将两个数据源Union all这样我们可以用Union 好的数据做统计。
最后的结果就是筛选条件，按数据透视表的样式来形成新的表。
附件于7月26日已作更新。
[ 本帖最后由 Scarlett_88 于 2009-7-26 15:28 编辑 ] |
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-7-22 23:41
发表于 2009-7-23 12:04
