统计统计问题
有表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