求助导出并自动纵横统计 - 编程论坛

#2

星光悠蓝2023-08-09 14:12

数据量非常庞大吗? Excel处理这种数据最高效。

#3

sdta2023-08-09 15:29

ＶＦＰ中处理好再导入ＥＸＣＥＬ中

#4

schtg2023-08-09 16:03

回复楼主 chychychy

可先确定 bmddm 类型，然后再汇总统计，这是一思路，仅供参考！

#5

fyyylyl2023-08-09 16:26

试试：
CLOSE DATABASES
USE 2023信息 alia 信息
cStr1="SELECT xkkmzhdm,CNT(*) Zong"
cStr2="INSERT INTO Huizong(Xkkmzhdm,Zong"
cStr3="SELECT [合计],SUM(Zong)"
SELECT Bmddm,CNT(*) FROM 信息 GROUP BY Bmddm INTO CURSOR Bm
SCAN
    cStr1=cStr1+",SUM(IIF(!EMPTY("+Bmddm+")  AND Bmddm=["+Bmddm+"],1,0)) N"+Bmddm
    cStr2=cStr2+",N"+Bmddm
    cStr3=cStr3+",SUM(N"+Bmddm+")"
ENDSCAN
cStr1=cStr1+" FROM 信息 GROUP BY Xkkmzhdm INTO CURSOR Huizong READWRITE "
cStr2=cStr2+")"
cStr3=cStr3+" FROM Huizong"
ExecScript(cStr1)
ExecScript(cStr2+cStr3)
SELECT Huizong
BROWSE

#6

吹水佬2023-08-09 21:05

只有本站会员才能查看附件，请登录

程序代码：

USE 2023信息 ALIAS b2023
SELECT DISTINCT bmddm    FROM b2023 INTO ARRAY arr_bmddm
SELECT DISTINCT xkkmzhdm FROM b2023 ORDER BY xkkmzhdm INTO ARRAY arr_xkkmzhdm
cmd = "CREATE CURSOR tt (xkkmzhdm c(8),zong I"
FOR i=1 TO ALEN(arr_bmddm)
    cmd = cmd + "," + "_" + arr_bmddm[i] + " I"
ENDFOR
cmd = cmd + ")"
EXECSCRIPT(cmd)
APPEND FROM ARRAY arr_xkkmzhdm
INDEX on xkkmzhdm TAG xkkmzhdm
SELECT b2023
SET RELATION TO xkkmzhdm INTO "tt"
SCAN
    REPLACE tt.zong WITH tt.zong + 1, ("tt._"+b2023.bmddm) WITH EVALUATE("tt._"+b2023.bmddm) + 1
ENDSCAN
SELECT * FROM tt

[此贴子已经被作者于2023-8-9 21:09编辑过]

#7

z126892023-08-10 09:53

以下是引用吹水佬在2023-8-9 21:05:08的发言：

USE 2023信息 ALIAS b2023
SELECT DISTINCT bmddm    FROM b2023 INTO ARRAY arr_bmddm
SELECT DISTINCT xkkmzhdm FROM b2023 ORDER BY xkkmzhdm INTO ARRAY arr_xkkmzhdm
cmd = "CREATE CURSOR tt (xkkmzhdm c(8),zong I"
FOR i=1 TO ALEN(arr_bmddm)
    cmd = cmd + "," + "_" + arr_bmddm[i] + " I"
ENDFOR
cmd = cmd + ")"
EXECSCRIPT(cmd)
APPEND FROM ARRAY arr_xkkmzhdm
INDEX on xkkmzhdm TAG xkkmzhdm
SELECT b2023
SET RELATION TO xkkmzhdm INTO "tt"
SCAN
    REPLACE tt.zong WITH tt.zong + 1, ("tt._"+b2023.bmddm) WITH EVALUATE("tt._"+b2023.bmddm) + 1
ENDSCAN
SELECT * FROM tt

学习了！

#8

schtg2023-08-10 10:20

回复 6楼吹水佬

高，学习啦，谢谢！

#9

chychychy2023-08-11 15:02

@fyyylyl

@吹水佬

因出发未能用电脑，刚测试两个都符合要求，太感谢了，有些语句还不是很懂含义，继续学习。

#10

chychychy2023-08-11 15:18

@fyyylyl @吹水佬本想结贴提供程序代码的两位高手，但不小心结贴散分点错了，请见谅。

#11

chychychy2023-08-11 15:26

回复 5楼 fyyylyl

能简单给解释一下语句的含义吗？有些不大明白的地方查看了帮助文件也没大明白

#12

fyyylyl2023-08-11 16:25

其实主要就是两条语句，假如Bmddm只有一个098209：
1、利用Select的分组计数功能取得各组数量
SELECT xkkmzhdm,CNT(*) Zong,SUM(IIF(!EMPTY(Bmddm) AND Bmddm=[098209],1,0) N098209 FROM 信息 GROUP BY Xkkmzhdm &&SUM(IIF(!EMPTY(Bmddm) AND Bmddm=[098209],1,0)——如果Bmddm为指定值则计数1，否则不计（计数0）；字段名不能以数字开头，所以加了一个N
2、插入合计
INSERT INTO Huizong(Xkkmzhdm,Zong,N098209) SELECT [合计],SUM(Zong),SUM(N098209) FROM Huizong
至于SELECT Bmddm,CNT(*) FROM 信息 GROUP BY Bmddm INTO CURSOR Bm 就是取得每个Bmddm名称，再利用循环语句将各个具体名称加入到上述两条语句中。

#13

chychychy2023-08-11 18:00

SELECT xkkmzhdm,CNT(*) Zong,SUM(IIF(!EMPTY(Bmddm) AND Bmddm=[098209],1,0)) N098209 FROM 信息 GROUP BY Xkkmzhdm
谢谢，这样明白一点了，但是用循环语句实现全自动查询，我一是还没消化，先拿来主义，晚上再消化吸收一下