表内容统计
有表A,字段为文本型
字段1 字段2 字段3 字段4
A B C D
B B C C
C C C C
E F G H
要求得出:
字段1 字段2 字段3 字段4 统计
A B C D 1A1B1C1D
B B C C 2B2C
C C C C 4C
E F G H 1E1F1G1H
做何做?谢谢!
[解决办法]
scan
iA=0
iB=0
iC=0
iD=0
for i=1 to fcount()
f=field(i)
do case
case "A "=&f
iA = iA + 1
case "B "=&f
iB = iB + 1
case "C "=&f
iC = iC + 1
case "D "=&f
iD = iD + 1
endcase
endfor
f=allt(str(iA))+ "A "+allt(str(iB))+ "B "+allt(str(iC))+ "C "+allt(str(iD))+ "D "
repl 统计 with f
endscan
[解决办法]
VFP9:
SELECT A.*,RR.ZD1 FROM TG A INNER JOIN (;
SELECT ID,STRTRAN(;
IIF(VAL(A1)> 0,A1+ 'A ', ' ')+IIF(VAL(B1)> 0,B1+ 'B ', ' ')+;
IIF(VAL(C1)> 0,C1+ 'C ', ' ')+;
IIF(VAL(D1)> 0,D1+ 'D ', ' ')+;
IIF(VAL(E1)> 0,E1+ 'E ', ' ')+;
IIF(VAL(F1)> 0,F1+ 'F ', ' ')+;
IIF(VAL(G1)> 0,G1+ 'G ', ' ')+;
IIF(VAL(H1)> 0,H1+ 'H ', ' '), ' ', ' ') AS ZD1;
,* FROM (;
SELECT ID,;
TRIM(STR(SUM(OCCURS( 'A ',ZD)))) AS A1,;
TRIM(STR(SUM(OCCURS( 'B ',ZD)))) AS B1,;
TRIM(STR(SUM(OCCURS( 'C ',ZD)))) AS C1,;
TRIM(STR(SUM(OCCURS( 'D ',ZD)))) AS D1,;
TRIM(STR(SUM(OCCURS( 'E ',ZD)))) AS E1,;
TRIM(STR(SUM(OCCURS( 'F ',ZD)))) AS F1,;
TRIM(STR(SUM(OCCURS( 'G ',ZD)))) AS G1,;
TRIM(STR(SUM(OCCURS( 'H ',ZD)))) AS H1;
FROM ;
(SELECT ID,TRIM(a1)+TRIM(A2)+TRIM(A3)+TRIM(A4) AS ZD FROM TG) G;
GROUP BY ID) F) RR ;
ON A.ID=RR.ID_A
浠ヤ笅鍙互鐢熸垚鑻ュ共涓存椂琛ㄥ啀杩炴帴
[解决办法]
lcString1= 'SELECT *,SPACE(254) AS 统计, '
USE TT
FOR I=1 TO FCOUNT()
lcString1=lcString1+ 'ALLTRIM( '+FIELD(I)+ ')+ '
ENDFOR
lcString1=LEFT(lcString1,LEN(lcString1)-1)+ ' AS 统计用 INTO TABLE 目标表 FROM 表A '
&lcString1
SELECT 目标表
SCAN
J=0
FOR I=1 TO FCOUNT()-2
lcString2=ALLTRIM(EVALUATE(FIELD(I)))
J=OCCURS(lcString2,统计用)
REPLACE 统计用 WITH STRTRAN(统计用,lcString2, ' ')
IF J> 0
REPLACE 统计 WITH ALLTRIM(统计)+TRANSFORM(J)+lcString2
ENDIF
ENDFOR
ENDSCAN
ALTER TABLE 目标表 DROP 统计用
