你這些是數據庫表,不是自由表,沒給我DBC文件。
授人以渔,不授人以鱼。
#DEFINE CR_LF CHR(13)+CHR(10) CLEAR ALL CLOSE DATABASES ALL SET TALK OFF && 有此语句运行速度加快 SET DEFAULT TO "D:\lyxc34" frm_Test = CREATEOBJECT("_Test") && 用下面自定义的_Test类建立窗体 frm_Test.Grid1.Init("ALLTRIM(xsb.bjmc) == '09护2'") && 将数据的过滤条件传递给表格控件,在你自己的表单中提交查询时使用本语句即可 frm_Test.Show && 显示窗体实例对象 READ EVENTS && 开启事务处理进行互动 RETURN && 程序在此结束 *----------------------- * 自定义窗体类 *----------------------- DEFINE CLASS _Test AS Form Caption = "学生成绩查询" WindowState = 2 && 启动时窗体最大化 ADD OBJECT Grid1 AS my_Grid NOINIT && 如果用可视化编程,不需本类代码,但你表单中的Grid必须继承自下面的my_Grid类 && 若如此,则在该Grid对象的Init事件中书写本处的Text1.Init代码 *----------------------- * 建立数据环境,应在自己的程序中自己处理 *----------------------- PROCEDURE Load USE xsb ORDER xh IN 0 && 学生表,以xh建立索引 USE kcb ORDER kch IN 0 && 课程表,以kch建立索引 USE cjb ORDER m_ID IN 0 && 成绩表,分别以xh、kch、xh+kch建立索引,以xh+kch为主索引 *-------------------------- * 数据库之间建立关联,这是关键点 *-------------------------- SET RELATION TO xh INTO cjb IN xsb SET RELATION TO kch INTO kcb IN cjb ENDPROC PROCEDURE UnLoad CLOSE DATABASES ALL ENDPROC PROCEDURE Destroy CLEAR EVENTS ENDPROC HIDDEN PROCEDURE Arrange WITH This.Grid1 .Top = 5 .Left = 5 .Height = This.Height - .Top - 5 .Width = This.Width - .Left - 5 ENDWITH ENDPROC PROCEDURE Activate This.Arrange && 窗体激发时不会调用Resize代码 ENDPROC PROCEDURE Resize This.Arrange && 调整窗体时控件跟随变化 ENDPROC *-------------------------- * 表格控件的初始化代码 *-------------------------- PROCEDURE Grid1.Init(tcFilter AS Character) LOCAL laColumns[5+RECCOUNT("kcb"),5] LOCAL lnIndex AS Integer *---------------------------- * 设定表格的栏目,标题名和顺序按自己心意增加或调整 * 注意头三栏的数据绑定法,用别名+字段名,Grid控件能够去不同的表取数据,不是非要将数据生成到同一张视图上的!因此,建立数据关联是重中之重。 * 只要数据库设计符合规范,数据就可以通过关联组合起来,这就是SQL查询所做的事,把我现在做的事接过去做了,但使用了大量的磁盘动作,还没有我现在所做的灵活。 * 你若有兴趣,不妨用SQL语句实现本程序的功能,看看速度如何。 * 表中没有字段的栏目,参照第4、5栏的用法,不用建立数据库 *---------------------------- laColumns[1,1] = "学号" laColumns[1,2] = "xsb.xh" laColumns[1,3] = 100 laColumns[2,1] = "姓名" laColumns[2,2] = "xsb.xm" laColumns[2,3] = 60 laColumns[3,1] = "班级名称" laColumns[3,2] = "xsb.bjmc" laColumns[3,3] = 60 laColumns[4,1] = "总成绩" laColumns[4,2] = "Get_TotalScore(xsb.xh)" laColumns[4,3] = 80 laColumns[4,4] = "Z" laColumns[4,5] = "99,999.99" laColumns[5,1] = "平均分" laColumns[5,2] = "Get_AverageScore(xsb.xh)" laColumns[5,3] = 80 laColumns[5,4] = "Z" laColumns[5,5] = "999.99" GOTO TOP IN kcb lnIndex = 5 DO WHILE !EOF("kcb") lnIndex = lnIndex + 1 laColumns[lnIndex,1] = ALLTRIM(kcb.kcm) laColumns[lnIndex,2] = "Get_Score(xsb.xh, '" + kcb.kch + "')" laColumns[lnIndex,3] = 80 laColumns[lnIndex,4] = "Z" laColumns[lnIndex,5] = "999.99" SKIP IN kcb ENDDO WITH This SET FILTER TO &tcFilter && 用 SET FILTER TO 过滤语句控制Grid表格显示的数据 .Set_Columns(@laColumns, "xsb") && 使用数组设置Grid的各栏,第二个参数指定表格的数据来源,没本行的Grid等于BROWSE .ReadOnly = .T. .LockColumns = 3 .HeaderHeight = 35 .RowHeight = 25 ENDWITH *------------------------- * 删除没有分数的栏(删不删随便,我觉得删掉好看一点) *------------------------- WITH This lnIndex = 5 DO WHILE lnIndex <= .ColumnCount WITH .Columns(lnIndex).Header1 IF LENC(.Caption) > 5 .Caption = Wrap_String(.Caption, 5) .WordWrap = .T. ENDIF ENDWITH lcString = .Columns(lnIndex).ControlSource LOCAL lcSum AS Number SUM &lcString ALL TO lnSum IF lnSum > 0 lnIndex = lnIndex + 1 ELSE .DeleteColumn(lnIndex) ENDIF ENDDO ENDWITH GOTO TOP IN xsb ENDPROC ENDDEFINE *---------------------------- * 以下所有代码放在任一PRG文件中 * 在需要时SET PROCEDURE TO 打开它即可用 * 放在Project则内联在程序中 *---------------------------- *---------------------------- * 根据学号和课程号提取成绩 * 参数:tcID -- 学生学号 * tcCourseID -- 课程号 * 返回:成绩分数 *---------------------------- FUNCTION Get_Score(tcID AS Character, tcCourseID AS Character) AS Number RETURN IIF(SEEK(tcID + tcCourseID, "cjb", "m_ID"), cjb.cj, 0.0) ENDFUNC *---------------------------- * 根据学号提取总分数 * 参数:tcID -- 学生学号 * 返回:总分 *---------------------------- FUNCTION Get_TotalScore(tcID AS Character) AS Number LOCAL lnTotal AS Number lnTotal = 0.0 IF SEEK(tcID, "cjb", "xh") DO WHILE cjb.xh == tcID lnTotal = lnTotal + cjb.cj SKIP IN cjb ENDDO ENDIF RETURN lnTotal ENDFUNC *---------------------------- * 根据学号计算平均数 * 参数:tcID -- 学生学号 * 返回:平均分 *---------------------------- FUNCTION Get_AverageScore(tcID AS Character) AS Number LOCAL lnTotal AS Number, lnCount AS Integer lnTotal = 0.0 lnCount = 0 IF SEEK(tcID, "cjb", "xh") DO WHILE cjb.xh == tcID IF !EMPTY(cjb.cj) lnTotal = lnTotal + cjb.cj lnCount = lnCount + 1 ENDIF SKIP IN cjb ENDDO ENDIF RETURN IIF(lnCount > 0, lnTotal / lnCount, 0.0) ENDFUNC *---------------------------- * 将字符串按指定长度断行 * 参数:tcString -- 需要处理的字符串 * tnWidth -- 每行容纳的汉字数(每个汉字按1计算) *---------------------------- FUNCTION Wrap_String(tcString AS Character, tnWidth AS Integer) AS Character LOCAL lcBuffer AS Character lcBuffer = "" DO WHILE !EMPTY(tcString) lcBuffer = lcBuffer + LEFTC(tcString, tnWidth) tcString = SUBSTRC(tcString, tnWidth + 1) IF !EMPTY(tcString) lcBuffer = lcBuffer + CR_LF ENDIF ENDDO RETURN lcBuffer ENDFUNC *---------------------- * 自定义表格类 *---------------------- DEFINE CLASS my_Grid AS Grid Themes = .F. DeleteMark = .F. RecordMark = .F. HighlightStyle = 2 HighlightRowLineWidth = 3 *--------------------------- * 设置列內容 * 参数:aColumns -- 列内容数组 * Column1 -- 栏标题 * Column2 -- 栏数据绑定源 * Column3 -- 栏宽度 * Column4 -- Format 掩码 * Column5 -- ImputMask 掩码 *--------------------------- PROCEDURE Set_Columns(aColumns[] AS Character, cSource AS Character) LOCAL nIndex AS Integer LOCAL cBuffer AS Character WITH This .RecordSourceType = 1 .RecordSource = cSource .ColumnCount = ALEN(aColumns,1) FOR nIndex = 1 TO .ColumnCount WITH .Columns(nIndex) WITH .Header1 .Caption = aColumns[nIndex,1] .FontBold = .T. .Alignment = 2 ENDWITH .ControlSource = aColumns[nIndex,2] IF !EMPTY(aColumns[nIndex,3]) .Width = aColumns[nIndex,3] ENDIF IF !EMPTY(aColumns[nIndex,4]) .Format = aColumns[nIndex,4] ENDIF IF !EMPTY(aColumns[nIndex,5]) .InputMask = aColumns[nIndex,5] ENDIF IF INLIST(VARTYPE(EVALUATE(.ControlSource)), "N", "Y") .Alignment = 1 ENDIF ENDWITH NEXT ENDWITH ENDPROC ENDDEFINE