首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > VFP >

统计统计有关问题

2012-02-08 
统计统计问题有表A:字段1字段201A01B01D02C02D02D03B03B.............要求得表B:XHABCD总计01110130200123

统计统计问题
有表A:
字段1       字段2  
01               A
01               B
01               D
02               C
02               D
02               D
03               B
03               B
.............

要求得表B:
  XH       A       B       C       D       总计
  01       1       1       0       1         3  
  02       0       0       1       2         3
  03       0       2       0       0         2
小计     1       3       1       3         8



[解决办法]
&&根据“十三豆”的“select 对两个表的操作”
&&参见:http://community.csdn.net/Expert/topic/5603/5603321.xml?temp=.8400843
&&将以下内容生成.prg文件(也许需要修改源表和结果表的名字),不做过多解释了
clear
create table a (field1 c(4),field2 c(1)) &&源表名a.dbf
insert into a values ( "01 ", "A ")
insert into a values ( "01 ", "B ")
insert into a values ( "01 ", "D ")
insert into a values ( "02 ", "C ")
insert into a values ( "02 ", "D ")
insert into a values ( "02 ", "D ")
insert into a values ( "03 ", "B ")
insert into a values ( "03 ", "B ")
SELECT * FROM A

select *,count(*) from a group by field1,field2 into cursor temp2
select field2 from a group by field2 into cursor temp3

select temp3
ls1= " select distinct field1 as xh "
scan
ls1=ls1+ ', 0000000 as '+alltrim(field2)+ ' '
endscan
ls1=ls1+ ', 0000000 as 总计 from a into table b_temp '
&ls1


select temp2
scan
ls2= 'update b_temp set '
ls2=ls2+ field2 + ' = ' +str(cnt,1)+ [ where ']+field1+[ '=xh]
&ls2
endscan


select temp3
ls3= " update b_temp set 总计=0 "
ls4=[select * from b_temp union select '小计 ' as xh ]
scan
ls3=ls3+ "+ "+alltrim(field2)
ls4=ls4+ ', sum( '+alltrim(field2)+[) as ]+alltrim(field2)+[ ]
endscan
ls4=ls4+ ', sum(总计) as 总计 from b_temp into table b ' &&目标表名b.dbf
&ls3
&ls4

select * from b
close all

热点排行