作者在 2015-01-14 20:49:53 发布以下内容
来自：http://user.qzone.qq.com/402118451/blog/1387457684
交叉表查询分为两种：
（1）静态交叉表
（2）动态交叉表
准备测试数据
CREATE CURSOR test (ksh c(12),xm c(8),km c(8),fs n(3,0))
INSERT INTO test value('13001','张三','语文',45)
INSERT INTO test value('13001','张三','数学',48)
INSERT INTO test value('13001','张三','英语',65)
INSERT INTO test value('13001','张三','物理',86)
INSERT INTO test value('13001','张三','化学',35)
INSERT INTO test value('13002','李四','语文',45)
INSERT INTO test value('13002','李四','数学',48)
INSERT INTO test value('13002','李四','英语',65)
INSERT INTO test value('13002','李四','物理',86)
INSERT INTO test value('13002','李四','化学',35)
INSERT INTO test value('13003','王五','语文',45)
INSERT INTO test value('13003','王五','数学',48)
INSERT INTO test value('13003','王五','英语',65)
INSERT INTO test value('13003','王五','物理',86)
INSERT INTO test value('13003','王五','化学',35)
静态交叉表的意思是生成的结果列数（字段）固定个数，这个问题很容易解决
SELECT ksh,xm;
,SUM(IIF(km='语文',fs,0)) as yw;
,SUM(IIF(km='数学',fs,0)) as sx;
,SUM(IIF(km='英语',fs,0)) as yy;
,SUM(IIF(km='物理',fs,0)) as wl;
,SUM(IIF(km='化学',fs,0)) as hx;
FROM test GROUP BY ksh,xm
即把每个列枚举出来，进行查询。 其它数据库大致也是这样，只有一点区别，如MS SQL的写法是
（1）静态交叉表
（2）动态交叉表
准备测试数据
CREATE CURSOR test (ksh c(12),xm c(8),km c(8),fs n(3,0))
INSERT INTO test value('13001','张三','语文',45)
INSERT INTO test value('13001','张三','数学',48)
INSERT INTO test value('13001','张三','英语',65)
INSERT INTO test value('13001','张三','物理',86)
INSERT INTO test value('13001','张三','化学',35)
INSERT INTO test value('13002','李四','语文',45)
INSERT INTO test value('13002','李四','数学',48)
INSERT INTO test value('13002','李四','英语',65)
INSERT INTO test value('13002','李四','物理',86)
INSERT INTO test value('13002','李四','化学',35)
INSERT INTO test value('13003','王五','语文',45)
INSERT INTO test value('13003','王五','数学',48)
INSERT INTO test value('13003','王五','英语',65)
INSERT INTO test value('13003','王五','物理',86)
INSERT INTO test value('13003','王五','化学',35)
静态交叉表的意思是生成的结果列数（字段）固定个数，这个问题很容易解决
SELECT ksh,xm;
,SUM(IIF(km='语文',fs,0)) as yw;
,SUM(IIF(km='数学',fs,0)) as sx;
,SUM(IIF(km='英语',fs,0)) as yy;
,SUM(IIF(km='物理',fs,0)) as wl;
,SUM(IIF(km='化学',fs,0)) as hx;
FROM test GROUP BY ksh,xm
即把每个列枚举出来，进行查询。 其它数据库大致也是这样，只有一点区别，如MS SQL的写法是
select ksh,xm,
sum(case km when '语文' then fs end) as yw,
sum(case km when '数学' then fs end) as sx,
sum(case km when '英语' then fs end) as yy,
sum(case km when '物理' then fs end) as wl,
sum(case km when '化学' then fs end) as hx,
from test group by ks
与VFP的区别仅在于用case...when...then...end与IIF()，而MySql的写法是和MS SQL的写法可以说是完全相同的。
动态交叉表是指生成的结果表列数（字段）不定， 这个有点小复杂，得先解决列数的问题才行。来看看MS SQL是如何解决的
declare @sql varchar(8000)
set @sql =''
select @sql = @sql +','+km+'=
sum(case km when'''+km+'''then fs else 0 end)'
from test group by km
exec ('select ksh,xm'+@sql+',from test group by ksh,xm')
从这段代码中很容易看出，在MS SQL中， 解决动态交叉表中的动态（不定列）问题是通过拼串，通过拼串，拼出相当于静态交叉表用的查询代码形式，再执行查询。那VFP中是不是也可以这样呢？仿照MS SQL的做法，做了下边代码
*拼串
DIMENSION cstr(1000)
cmdstr=''
SELECT ",SUM(IIF(km='"+ALLTRIM(km)+"',fs,0)) as "+ALLTRIM(km) FROM test GROUP BY km INTO ARRAY cstr
FOR i=1 TO ALEN(cstr)
cmdstr=cmdstr+ALLTRIM(cstr(i))
ENDFOR
*查询
EXECSCRIPT('SELECT ksh,xm'+cmdstr+' from test group by ksh,xm')
显然，动态交叉表比静态的复杂了许多，，也不知道有没有一个函数，可以让VFP拼串时的代码更简单一些？？？
2014年2月28日
习惯了利用sum对数值型字段进行交叉表查询，想当然的以为都是这个样子了，今天在群中交流时遭遇了不一样的情况，经过一番试验后，对交叉表查询进行完善。
在此就仅以静态交叉表进行说明，动态的是一样的。
与以前的情况一样，区别只是表中的fs改为字符型的，即
CREATE CURSOR test (ksh c(12),xm c(8),km c(8),fs c(3,0))
其它的不变，当fs字段为字符型时，
SELECT ksh,xm;
,SUM(IIF(km='语文',fs,0)) as yw;
,SUM(IIF(km='数学',fs,0)) as sx;
,SUM(IIF(km='英语',fs,0)) as yy;
,SUM(IIF(km='物理',fs,0)) as wl;
,SUM(IIF(km='化学',fs,0)) as hx;
FROM test GROUP BY ksh,xm
这句是不能执行的，问题出在了sum不能对字符数据进行求和，怎么办呢？我们会自然想到字符型数据字串拼接，而VFP中没有一个函数可以对一个字符型字段完成这个操作，而这里需要一个“聚合函数”完成这个操作，VFP却又不能自定义聚合函数供select使用，在VFP的帮助中明确列出了select可以使用的函数，
那么，也只是能在这几个函数上边找办法了，，
而这五个函数中可以使用到字符型字段也就只有cnt，min，max这三个，cnt肯定是行不通的，到此能用的就只有min和max，而max应该是可行的，因为max('',' ','asdf',space(10))返回的是asdf，到此问题应该可以得到解决了，命令如下
SELECT ksh,xm;
,max(IIF(km='语文',fs,SPACE(3))) as yw;
,max(IIF(km='数学',fs,SPACE(3))) as sx;
,max(IIF(km='英语',fs,SPACE(3))) as yy;
,max(IIF(km='物理',fs,SPACE(3))) as wl;
,max(IIF(km='化学',fs,SPACE(3))) as hx;
FROM test GROUP BY ksh,xm
在这条命令里尤其需要注意的是：SPACE(3)，此处返回的空格串的长度一定要和fs的字段宽度一样或者大一些，否则结果可能不正确。至于原因就不说了。
<End>
sum(case km when '语文' then fs end) as yw,
sum(case km when '数学' then fs end) as sx,
sum(case km when '英语' then fs end) as yy,
sum(case km when '物理' then fs end) as wl,
sum(case km when '化学' then fs end) as hx,
from test group by ks
与VFP的区别仅在于用case...when...then...end与IIF()，而MySql的写法是和MS SQL的写法可以说是完全相同的。
动态交叉表是指生成的结果表列数（字段）不定， 这个有点小复杂，得先解决列数的问题才行。来看看MS SQL是如何解决的
declare @sql varchar(8000)
set @sql =''
select @sql = @sql +','+km+'=
sum(case km when'''+km+'''then fs else 0 end)'
from test group by km
exec ('select ksh,xm'+@sql+',from test group by ksh,xm')
从这段代码中很容易看出，在MS SQL中， 解决动态交叉表中的动态（不定列）问题是通过拼串，通过拼串，拼出相当于静态交叉表用的查询代码形式，再执行查询。那VFP中是不是也可以这样呢？仿照MS SQL的做法，做了下边代码
*拼串
DIMENSION cstr(1000)
cmdstr=''
SELECT ",SUM(IIF(km='"+ALLTRIM(km)+"',fs,0)) as "+ALLTRIM(km) FROM test GROUP BY km INTO ARRAY cstr
FOR i=1 TO ALEN(cstr)
cmdstr=cmdstr+ALLTRIM(cstr(i))
ENDFOR
*查询
EXECSCRIPT('SELECT ksh,xm'+cmdstr+' from test group by ksh,xm')
显然，动态交叉表比静态的复杂了许多，，也不知道有没有一个函数，可以让VFP拼串时的代码更简单一些？？？
2014年2月28日
习惯了利用sum对数值型字段进行交叉表查询，想当然的以为都是这个样子了，今天在群中交流时遭遇了不一样的情况，经过一番试验后，对交叉表查询进行完善。
在此就仅以静态交叉表进行说明，动态的是一样的。
与以前的情况一样，区别只是表中的fs改为字符型的，即
CREATE CURSOR test (ksh c(12),xm c(8),km c(8),fs c(3,0))
其它的不变，当fs字段为字符型时，
SELECT ksh,xm;
,SUM(IIF(km='语文',fs,0)) as yw;
,SUM(IIF(km='数学',fs,0)) as sx;
,SUM(IIF(km='英语',fs,0)) as yy;
,SUM(IIF(km='物理',fs,0)) as wl;
,SUM(IIF(km='化学',fs,0)) as hx;
FROM test GROUP BY ksh,xm
这句是不能执行的，问题出在了sum不能对字符数据进行求和，怎么办呢？我们会自然想到字符型数据字串拼接，而VFP中没有一个函数可以对一个字符型字段完成这个操作，而这里需要一个“聚合函数”完成这个操作，VFP却又不能自定义聚合函数供select使用，在VFP的帮助中明确列出了select可以使用的函数，
那么，也只是能在这几个函数上边找办法了，，
而这五个函数中可以使用到字符型字段也就只有cnt，min，max这三个，cnt肯定是行不通的，到此能用的就只有min和max，而max应该是可行的，因为max('',' ','asdf',space(10))返回的是asdf，到此问题应该可以得到解决了，命令如下
SELECT ksh,xm;
,max(IIF(km='语文',fs,SPACE(3))) as yw;
,max(IIF(km='数学',fs,SPACE(3))) as sx;
,max(IIF(km='英语',fs,SPACE(3))) as yy;
,max(IIF(km='物理',fs,SPACE(3))) as wl;
,max(IIF(km='化学',fs,SPACE(3))) as hx;
FROM test GROUP BY ksh,xm
在这条命令里尤其需要注意的是：SPACE(3)，此处返回的空格串的长度一定要和fs的字段宽度一样或者大一些，否则结果可能不正确。至于原因就不说了。
<End>