你没有做“职务表”吗？不做职务表，自动筛选的排名是无序的，这不是那么好。

[ 本帖最后由 TonyDeng 于 2012-8-29 14:05 编辑 ]

有职务表，但是我只用了表中的职务码，GZ02表我已经取上行政职务码了，sdta版主给提的意见我看了，副主任那几列是必须分开的！

[ 本帖最后由 hyhosd 于 2012-8-29 16:28 编辑 ]

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

谢谢sdta版主，我先看看你给的代码！

职务代码太乱了

你的原始表中有两个主任是重复的，怎么回事？



[ 本帖最后由 TonyDeng 于 2012-8-29 16:12 编辑 ]

两个主任是错的，每个机构只能有一个，我把数据给弄错了！

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 

[ 本帖最后由 TonyDeng 于 2012-8-29 16:29 编辑 ]