你没有做“职务表”吗?不做职务表,自动筛选的排名是无序的,这不是那么好。
[ 本帖最后由 TonyDeng 于 2012-8-29 14:05 编辑 ]
[ 本帖最后由 TonyDeng 于 2012-8-29 14:05 编辑 ]
授人以渔,不授人以鱼。
CREATE CURSOR TABA (JGID C(9),行政职务 C(6),姓名 C(6)) INSERT INTO TABA VALUES ("140207000","理事长","姓名1") INSERT INTO TABA VALUES ("140207000","监事长","姓名2") INSERT INTO TABA VALUES ("140207000","主任","姓名3") INSERT INTO TABA VALUES ("140207000","副主任","姓名4") INSERT INTO TABA VALUES ("140207000","副主任","姓名5") INSERT INTO TABA VALUES ("140207000","副主任","姓名6") INSERT INTO TABA VALUES ("140207000","副主任","姓名7") INSERT INTO TABA VALUES ("140207000","副主任","姓名8") INSERT INTO TABA VALUES ("140206000","主任","姓名9") INSERT INTO TABA VALUES ("140206000","副主任","姓名10") INSERT INTO TABA VALUES ("140206000","副主任","姓名11") INSERT INTO TABA VALUES ("140201000","理事长","姓名12") INSERT INTO TABA VALUES ("140201000","副主任","姓名13") *------------------------------------------------------ *--- 生成[职数]数组 SELECT JGID,COUNT(*) AS 职数 FROM TABA GROUP BY JGID INTO ARRAY ATABA_1 *--- 生成[行政职务] 职数 临时表 SELECT JGID,行政职务,COUNT(*) AS 职数 FROM TABA GROUP BY JGID,行政职务 INTO CURSOR TABA_1 *--- 职位最多的人数 CALCULATE MAX(职数) TO ZSMAX *--- 生成[行政职务]数组 SELECT DISTINCT 行政职务 FROM TABA INTO ARRAY ATABA_2 *--- 生成按[行政职务]排列 SELECT JGID,行政职务,CAST(FUN(JGID,行政职务) AS C(40)) AS 姓名 FROM TABA GROUP BY JGID,行政职务 INTO ARRAY ATABA_3 *--- 合计 SELECT SPACE(10) AS 合计,COUNT(*) FROM TABA UNION (SELECT 行政职务,COUNT(*) FROM TABA GROUP BY 行政职务) INTO ARRAY ATABA_4 *--- 生成查询临时表结构 CSTR="" FOR I=1 TO ALEN(ATABA_2,1) CSTR=CSTR+ALLTRIM(ATABA_2(I,1))+[ C(]+TRANSFORM(7*ZSMAX-1)+[)]+IIF(I<ALEN(ATABA_2,1),[,],[]) ENDFOR CREATE CURSOR TABB (JGID C(10),职数 N(4),&CSTR) *--- 添加数据 APPEND FROM ARRAY ATABA_1 SCAN FOR I=3 TO FCOUNT() FOR J=1 TO ALEN(ATABA_3,1) REPLACE (FIELD(I)) WITH ATABA_3(J,3) FOR ALLTRIM(JGID)==ALLTRIM(ATABA_3(J,1)) AND (FIELD(I))=ALLTRIM(ATABA_3(J,2)) ENDFOR ENDFOR ENDSCAN *--- 生成[合计]行 INSERT INTO TABB (JGID) VALUES ("合 计") FOR I=2 TO FCOUNT() REPLACE RECORD RECCOUNT() (FIELD(I)) WITH IIF(TYPE((FIELD(I)))="N",ATABA_4(I-1,2),TRANSFORM(ATABA_4(I-1,2))) ENDFOR BROWSE *--- 自定义函数 ---* FUNCTION FUN PARAMETER CJGID,CNAME CSTR="" SELECT TABA SCAN FOR ALLTRIM(JGID)==ALLTRIM(CJGID) AND ALLTRIM(行政职务)==ALLTRIM(CNAME) CSTR=CSTR+ALLTRIM(姓名)+"-" ENDSCAN RETURN LEFT(CSTR,LEN(CSTR)-1) ENDFUNC
CLEAR ALL CLOSE TABLES ALL SET SAFETY OFF Form1 = NEWOBJECT("Form_Gz02") Form1.Show READ EVENTS RETURN *----------------------------- * 创建统计表 *----------------------------- PROCEDURE CreateNewTable LOCAL laStructure[1,18], lnIndex SELECT gz02 COPY STRUCTURE EXTENDED TO _Structure USE _Structure EXCLUSIVE IN 0 SELECT _Structure GOTO 2 DELETE REST PACK SELECT zwb lnIndex = 0 SCAN ALL SELECT _Structure APPEND BLANK IF ALLTRIM(zwb.Name) != "副主任" REPLACE _Structure.Field_Name WITH ALLTRIM(zwb.Name) ELSE lnIndex = lnIndex + 1 REPLACE _Structure.Field_Name WITH "副主任_" + TRANSFORM(lnIndex) ENDIF REPLACE _Structure.Field_Type WITH "C", _Structure.Field_Len WITH 20 ENDSCAN USE IN _Structure CREATE result FROM _Structure ENDPROC *----------------------------- * 统计数据 *----------------------------- PROCEDURE Statistics LOCAL lnIndex SELECT gz02 SCAN ALL SELECT result LOCATE ALL FOR ALLTRIM(EVALUATE(FIELD(1, "result"))) == ALLTRIM(gz02.jgid) IF !FOUND("result") APPEND BLANK IN result REPLACE (FIELD(1, "result")) WITH ALLTRIM(gz02.jgid) ENDIF IF ALLTRIM(gz02.行政职务) != "副主任" REPLACE (ALLTRIM(gz02.行政职务)) WITH ALLTRIM(gz02.姓名) ELSE LOCAL lcFieldName lnIndex = 1 lcFieldName = "result.副主任_" + TRANSFORM(lnIndex) DO WHILE !EMPTY(EVALUATE(lcFieldName)) lnIndex = lnIndex + 1 lcFieldName = "result.副主任_" + TRANSFORM(lnIndex) ENDDO REPLACE (lcFieldName) WITH ALLTRIM(gz02.姓名) ENDIF ENDSCAN LOCAL lnTotal SELECT result APPEND BLANK REPLACE (FIELD(1)) WITH "合计" FOR lnIndex = 2 TO FCOUNT() COUNT ALL FOR !EMPTY(EVALUATE(FIELD(lnIndex))) TO lnTotal GOTO BOTTOM REPLACE (FIELD(lnIndex)) WITH TRANSFORM(lnTotal, "@Z 9") NEXT ENDPROC *----------------------------- * 呈现窗体类定义 *----------------------------- DEFINE CLASS Form_Gz02 AS Form Caption = "hyhosd 的问题" Width = 800 Height = 600 AutoCenter = .T. ADD OBJECT Grid1 AS Grid PROCEDURE Load USE zwb EXCLUSIVE IN 0 USE gz02 EXCLUSIVE IN 0 CreateNewTable() ENDPROC PROCEDURE Unload CLOSE TABLES ALL ENDPROC PROCEDURE Destroy CLEAR EVENTS ENDPROC PROCEDURE Activate ThisForm.Arrange ENDPROC PROCEDURE Resize ThisForm.Arrange ENDPROC PROCEDURE Arrange WITH ThisForm.Grid1 .Top = 5 .Left = 5 .Width = ThisForm.Width - .Left - 5 .Height = ThisForm.Height - .Top - 5 ENDWITH ENDPROC PROCEDURE Grid1.Init Statistics() GOTO TOP IN result WITH This .ReadOnly = .T. .RecordSourceType = 1 .RecordSource = "result" LOCAL lnIndex FOR lnIndex = 1 TO .ColumnCount IF "副主任" $ .Columns(lnIndex).Header1.Caption .Columns(lnIndex).Header1.Caption = "副主任" ENDIF NEXT .SetAll("Width", 80, "Column") ENDWITH ENDPROC ENDDEFINE