我已经测试了。
select bj,count(语文) as 语文二本 from 理科 where 语文 >= 80 and 语文<100 group by bj INTO dbf yw2b
select bj,count(语文) as 语文本科 from 理科 where 语文 >= 80 group by bj INTO dbf ywbk
貌似输出很多库。然后在合并？很笨吧。
2020-01-21 21:32
2020-01-21 23:12
2020-01-22 01:07
2020-01-22 05:05
程序代码：repl all bm with "英才" for bj="01" repl all bm with "英才" for bj="02" repl all bm with "英才" for bj="03" repl all bm with "英才" for bj="04" repl all bm with "英才" for bj="05" repl all bm with "1部" for bj="06" repl all bm with "1部" for bj="07" repl all bm with "1部" for bj="08" repl all bm with "1部" for bj="09" repl all bm with "1部" for bj="10" repl all bm with "1部" for bj="11" repl all bm with "1部" for bj="12" repl all bm with "1部" for bj="13" repl all bm with "1部" for bj="14" repl all bm with "1部" for bj="15" repl all bm with "1部" for bj="16" repl all bm with "1部" for bj="17" repl all bm with "1部" for bj="18" repl all bm with "1部" for bj="19" repl all bm with "2部" for bj="20" repl all bm with "2部" for bj="21" repl all bm with "2部" for bj="22" repl all bm with "2部" for bj="23" repl all bm with "2部" for bj="24" repl all bm with "2部" for bj="25" repl all bm with "2部" for bj="26" repl all bm with "2部" for bj="27" repl all bm with "2部" for bj="28" repl all bm with "2部" for bj="29" repl all bm with "2部" for bj="30" repl all bm with "2部" for bj="31" repl all bm with "2部" for bj="32" repl all bm with "2部" for bj="33" repl all bm with "3部" for bj="34" repl all bm with "3部" for bj="35" repl all bm with "3部" for bj="36" repl all bm with "3部" for bj="37" repl all bm with "3部" for bj="38" repl all bm with "3部" for bj="39" repl all bm with "3部" for bj="40" repl all bm with "3部" for bj="41" repl all bm with "3部" for bj="42" repl all bm with "3部" for bj="43" repl all bm with "3部" for bj="44" repl all bm with "3部" for bj="45" repl all bm with "3部" for bj="46" repl all bm with "3部" for bj="47"
2020-01-22 09:33
程序代码：
set talk off
clear all
CLEAR
SET DELETED on
*设定分数线
yw1b=100
yw2b=88
sx1b=93
sx2b=60
yy1b=101
yy2b=66
wl1b=67
wl2b=38
hx1b=73
hx2b=48
sw1b=71
sw2b=49
zf1b=487
zf2b=356
*设定班级对应部
USE 理科
repl all bm with "英才" for bj="01"
repl all bm with "英才" for bj="02"
repl all bm with "英才" for bj="03"
repl all bm with "英才" for bj="04"
repl all bm with "英才" for bj="05"
repl all bm with "1部" for bj="06"
repl all bm with "1部" for bj="07"
repl all bm with "1部" for bj="08"
repl all bm with "1部" for bj="09"
repl all bm with "1部" for bj="10"
repl all bm with "1部" for bj="11"
repl all bm with "1部" for bj="12"
repl all bm with "1部" for bj="13"
repl all bm with "1部" for bj="14"
repl all bm with "1部" for bj="15"
repl all bm with "1部" for bj="16"
repl all bm with "1部" for bj="17"
repl all bm with "1部" for bj="18"
repl all bm with "1部" for bj="19"
repl all bm with "2部" for bj="20"
repl all bm with "2部" for bj="21"
repl all bm with "2部" for bj="22"
repl all bm with "2部" for bj="23"
repl all bm with "2部" for bj="24"
repl all bm with "2部" for bj="25"
repl all bm with "2部" for bj="26"
repl all bm with "2部" for bj="27"
repl all bm with "2部" for bj="28"
repl all bm with "2部" for bj="29"
repl all bm with "2部" for bj="30"
repl all bm with "2部" for bj="31"
repl all bm with "2部" for bj="32"
repl all bm with "2部" for bj="33"
repl all bm with "3部" for bj="34"
repl all bm with "3部" for bj="35"
repl all bm with "3部" for bj="36"
repl all bm with "3部" for bj="37"
repl all bm with "3部" for bj="38"
repl all bm with "3部" for bj="39"
repl all bm with "3部" for bj="40"
repl all bm with "3部" for bj="41"
repl all bm with "3部" for bj="42"
repl all bm with "3部" for bj="43"
repl all bm with "3部" for bj="44"
repl all bm with "3部" for bj="45"
repl all bm with "3部" for bj="46"
repl all bm with "3部" for bj="47"
*建统计库
select bj from 理科 group by bj INTO dbf tj
ALTER table tj ADD bm c(4)
ALTER table tj ADD 语文一本 n(6)
ALTER table tj ADD 语文二本 n(6)
ALTER table tj ADD 语文本科 n(6)
ALTER table tj ADD 数学一本 n(6)
ALTER table tj ADD 数学二本 n(6)
ALTER table tj ADD 数学本科 n(6)
ALTER table tj ADD 英语一本 n(6)
ALTER table tj ADD 英语二本 n(6)
ALTER table tj ADD 英语本科 n(6)
ALTER table tj ADD 物理一本 n(6)
ALTER table tj ADD 物理二本 n(6)
ALTER table tj ADD 物理本科 n(6)
ALTER table tj ADD 化学一本 n(6)
ALTER table tj ADD 化学二本 n(6)
ALTER table tj ADD 化学本科 n(6)
ALTER table tj ADD 生物一本 n(6)
ALTER table tj ADD 生物二本 n(6)
ALTER table tj ADD 生物本科 n(6)
ALTER table tj ADD 总分一本 n(6)
ALTER table tj ADD 总分二本 n(6)
ALTER table tj ADD 总分本科 n(6)
CLOSE TABLES
SELECT 2
USE 理科
INDEX ON bj TAG bj
SET ORDER TO TAG bj
SELECT 1
USE tj
SET RELATION TO bj INTO 2
REPLACE ALL bm WITH b->bm
appe blank
REPLACE bm WITH "英才"
appe blank
REPLACE bm WITH "1部"
appe blank
REPLACE bm WITH "2部"
appe blank
REPLACE bm WITH "3部"
appe blank
REPLACE bm WITH "总计"
CLOSE TABLES
stor 0 to nrs
sele 1
use 理科
sele 2
use tj
GO top
do while .not.eof()
xdm=bj
xbm=bm
n=RECNO()
sele 1
COUNT for bj=xdm to nrs
COUNT for 语文>=yw1b .and. bj=xdm to yw1
COUNT for 语文>=yw2b .and. 语文<yw1b .and. bj=xdm to yw2
COUNT for 语文>=yw2b .and. bj=xdm to yw3
COUNT for 数学>=sx1b .and. bj=xdm to sx1
COUNT for 数学>=sx2b .and. 数学<sx1b .and. bj=xdm to sx2
COUNT for 数学>=sx2b .and. bj=xdm to sx3
COUNT for 英语>=yy1b .and. bj=xdm to yy1
COUNT for 英语>=yy2b .and. 英语<yy1b .and. bj=xdm to yy2
COUNT for 英语>=yy2b .and. bj=xdm to yy3
COUNT for 物理>=wl1b .and. bj=xdm to wl1
COUNT for 物理>=wl2b .and. 物理<wl1b .and. bj=xdm to wl2
COUNT for 物理>=wl2b .and. bj=xdm to wl3
COUNT for 化学>=hx1b .and. bj=xdm to hx1
COUNT for 化学>=hx2b .and. 化学<hx1b .and. bj=xdm to hx2
COUNT for 化学>=hx2b .and. bj=xdm to hx3
COUNT for 生物>=sw1b .and. bj=xdm to sw1
COUNT for 生物>=sw2b .and. 生物<sw1b .and. bj=xdm to sw2
COUNT for 生物>=sw2b .and. bj=xdm to sw3
COUNT for 总分>=zf1b .and. bj=xdm to zf1
COUNT for 总分>=zf2b .and. 总分<zf1b .and. bj=xdm to zf2
COUNT for 总分>=zf2b .and. bj=xdm to zf3
SELECT 2
GO n
REPLACE 语文一本 with yw1,语文二本 with yw2,语文本科 with yw3
REPLACE 数学一本 with sx1,数学二本 with sx2,数学本科 with sx3
REPLACE 英语一本 with yy1,英语二本 with yy2,英语本科 with yy3
REPLACE 物理一本 with wl1,物理二本 with wl2,物理本科 with wl3
REPLACE 化学一本 with hx1,化学二本 with hx2,化学本科 with hx3
REPLACE 生物一本 with sw1,生物二本 with sw2,生物本科 with sw3
REPLACE 总分一本 with zf1,总分二本 with zf2,总分本科 with zf3
SKIP
ENDDO
SELECT 1
COUNT for 语文>=yw1b .and. bm="1部" to ywb1
COUNT for 语文>=yw2b .and. 语文<yw1b .and. bm="1部" to ywb2
COUNT for 语文>=yw2b .and. bm="1部" to ywb3
COUNT for 数学>=sx1b .and. bm="1部" to sxb1
COUNT for 数学>=sx2b .and. 数学<sx1b .and. bm="1部" to sxb2
COUNT for 数学>=sx2b .and. bm="1部" to sxb3
COUNT for 英语>=yy1b .and. bm="1部" to yyb1
COUNT for 英语>=yy2b .and. 英语<yy1b .and. bm="1部" to yyb2
COUNT for 英语>=yy2b .and. bm="1部" to yyb3
COUNT for 物理>=wl1b .and. bm="1部" to wlb1
COUNT for 物理>=wl2b .and. 物理<wl1b .and. bm="1部" to wlb2
COUNT for 物理>=wl2b .and. bm="1部" to wlb3
COUNT for 化学>=hx1b .and. bm="1部" to hxb1
COUNT for 化学>=hx2b .and. 化学<hx1b .and. bm="1部" to hxb2
COUNT for 化学>=hx2b .and. bm="1部" to hxb3
COUNT for 生物>=sw1b .and. bm="1部" to swb1
COUNT for 生物>=sw2b .and. 生物<sw1b .and. bm="1部" to swb2
COUNT for 生物>=sw2b .and. bm="1部" to swb3
COUNT for 总分>=zf1b .and. bm="1部" to zfb1
COUNT for 总分>=zf2b .and. 总分<zf1b .and. bm="1部" to zfb2
COUNT for 总分>=zf2b .and. bm="1部" to zfb3
SELECT 2
GO bott
SKIP -3
REPLACE 语文一本 with ywb1,语文二本 with ywb2,语文本科 with ywb3
REPLACE 数学一本 with sxb1,数学二本 with sxb2,数学本科 with sxb3
REPLACE 英语一本 with yyb1,英语二本 with yyb2,英语本科 with yyb3
REPLACE 物理一本 with wlb1,物理二本 with wlb2,物理本科 with wlb3
REPLACE 化学一本 with hxb1,化学二本 with hxb2,化学本科 with hxb3
REPLACE 生物一本 with swb1,生物二本 with swb2,生物本科 with swb3
REPLACE 总分一本 with zfb1,总分二本 with zfb2,总分本科 with zfb3
SELECT 1
COUNT for 语文>=yw1b .and. bm="2部" to ywb1
COUNT for 语文>=yw2b .and. 语文<yw1b .and. bm="2部" to ywb2
COUNT for 语文>=yw2b .and. bm="2部" to ywb3
COUNT for 数学>=sx1b .and. bm="2部" to sxb1
COUNT for 数学>=sx2b .and. 数学<sx1b .and. bm="2部" to sxb2
COUNT for 数学>=sx2b .and. bm="2部" to sxb3
COUNT for 英语>=yy1b .and. bm="2部" to yyb1
COUNT for 英语>=yy2b .and. 英语<yy1b .and. bm="2部" to yyb2
COUNT for 英语>=yy2b .and. bm="2部" to yyb3
COUNT for 物理>=wl1b .and. bm="2部" to wlb1
COUNT for 物理>=wl2b .and. 物理<wl1b .and. bm="2部" to wlb2
COUNT for 物理>=wl2b .and. bm="2部" to wlb3
COUNT for 化学>=hx1b .and. bm="2部" to hxb1
COUNT for 化学>=hx2b .and. 化学<hx1b .and. bm="2部" to hxb2
COUNT for 化学>=hx2b .and. bm="2部" to hxb3
COUNT for 生物>=sw1b .and. bm="2部" to swb1
COUNT for 生物>=sw2b .and. 生物<sw1b .and. bm="2部" to swb2
COUNT for 生物>=sw2b .and. bm="2部" to swb3
COUNT for 总分>=zf1b .and. bm="2部" to zfb1
COUNT for 总分>=zf2b .and. 总分<zf1b .and. bm="2部" to zfb2
COUNT for 总分>=zf2b .and. bm="2部" to zfb3
SELECT 2
GO bott
SKIP -2
REPLACE 语文一本 with ywb1,语文二本 with ywb2,语文本科 with ywb3
REPLACE 数学一本 with sxb1,数学二本 with sxb2,数学本科 with sxb3
REPLACE 英语一本 with yyb1,英语二本 with yyb2,英语本科 with yyb3
REPLACE 物理一本 with wlb1,物理二本 with wlb2,物理本科 with wlb3
REPLACE 化学一本 with hxb1,化学二本 with hxb2,化学本科 with hxb3
REPLACE 生物一本 with swb1,生物二本 with swb2,生物本科 with swb3
REPLACE 总分一本 with zfb1,总分二本 with zfb2,总分本科 with zfb3
SELECT 1
COUNT for 语文>=yw1b .and. bm="3部" to ywb1
COUNT for 语文>=yw2b .and. 语文<yw1b .and. bm="3部" to ywb2
COUNT for 语文>=yw2b .and. bm="3部" to ywb3
COUNT for 数学>=sx1b .and. bm="3部" to sxb1
COUNT for 数学>=sx2b .and. 数学<sx1b .and. bm="3部" to sxb2
COUNT for 数学>=sx2b .and. bm="3部" to sxb3
COUNT for 英语>=yy1b .and. bm="3部" to yyb1
COUNT for 英语>=yy2b .and. 英语<yy1b .and. bm="3部" to yyb2
COUNT for 英语>=yy2b .and. bm="3部" to yyb3
COUNT for 物理>=wl1b .and. bm="3部" to wlb1
COUNT for 物理>=wl2b .and. 物理<wl1b .and. bm="3部" to wlb2
COUNT for 物理>=wl2b .and. bm="3部" to wlb3
COUNT for 化学>=hx1b .and. bm="3部" to hxb1
COUNT for 化学>=hx2b .and. 化学<hx1b .and. bm="3部" to hxb2
COUNT for 化学>=hx2b .and. bm="3部" to hxb3
COUNT for 生物>=sw1b .and. bm="3部" to swb1
COUNT for 生物>=sw2b .and. 生物<sw1b .and. bm="3部" to swb2
COUNT for 生物>=sw2b .and. bm="3部" to swb3
COUNT for 总分>=zf1b .and. bm="3部" to zfb1
COUNT for 总分>=zf2b .and. 总分<zf1b .and. bm="3部" to zfb2
COUNT for 总分>=zf2b .and. bm="3部" to zfb3
SELECT 2
GO bott
SKIP -1
REPLACE 语文一本 with ywb1,语文二本 with ywb2,语文本科 with ywb3
REPLACE 数学一本 with sxb1,数学二本 with sxb2,数学本科 with sxb3
REPLACE 英语一本 with yyb1,英语二本 with yyb2,英语本科 with yyb3
REPLACE 物理一本 with wlb1,物理二本 with wlb2,物理本科 with wlb3
REPLACE 化学一本 with hxb1,化学二本 with hxb2,化学本科 with hxb3
REPLACE 生物一本 with swb1,生物二本 with swb2,生物本科 with swb3
REPLACE 总分一本 with zfb1,总分二本 with zfb2,总分本科 with zfb3
SELECT 1
COUNT for 语文>=yw1b .and. bm="英才" to ywb1
COUNT for 语文>=yw2b .and. 语文<yw1b .and. bm="英才" to ywb2
COUNT for 语文>=yw2b .and. bm="英才" to ywb3
COUNT for 数学>=sx1b .and. bm="英才" to sxb1
COUNT for 数学>=sx2b .and. 数学<sx1b .and. bm="英才" to sxb2
COUNT for 数学>=sx2b .and. bm="英才" to sxb3
COUNT for 英语>=yy1b .and. bm="英才" to yyb1
COUNT for 英语>=yy2b .and. 英语<yy1b .and. bm="英才" to yyb2
COUNT for 英语>=yy2b .and. bm="英才" to yyb3
COUNT for 物理>=wl1b .and. bm="英才" to wlb1
COUNT for 物理>=wl2b .and. 物理<wl1b .and. bm="英才" to wlb2
COUNT for 物理>=wl2b .and. bm="英才" to wlb3
COUNT for 化学>=hx1b .and. bm="英才" to hxb1
COUNT for 化学>=hx2b .and. 化学<hx1b .and. bm="英才" to hxb2
COUNT for 化学>=hx2b .and. bm="英才" to hxb3
COUNT for 生物>=sw1b .and. bm="英才" to swb1
COUNT for 生物>=sw2b .and. 生物<sw1b .and. bm="英才" to swb2
COUNT for 生物>=sw2b .and. bm="英才" to swb3
COUNT for 总分>=zf1b .and. bm="英才" to zfb1
COUNT for 总分>=zf2b .and. 总分<zf1b .and. bm="英才" to zfb2
COUNT for 总分>=zf2b .and. bm="英才" to zfb3
SELECT 2
GO bott
SKIP -4
REPLACE 语文一本 with ywb1,语文二本 with ywb2,语文本科 with ywb3
REPLACE 数学一本 with sxb1,数学二本 with sxb2,数学本科 with sxb3
REPLACE 英语一本 with yyb1,英语二本 with yyb2,英语本科 with yyb3
REPLACE 物理一本 with wlb1,物理二本 with wlb2,物理本科 with wlb3
REPLACE 化学一本 with hxb1,化学二本 with hxb2,化学本科 with hxb3
REPLACE 生物一本 with swb1,生物二本 with swb2,生物本科 with swb3
REPLACE 总分一本 with zfb1,总分二本 with zfb2,总分本科 with zfb3
SELECT 1
COUNT for 语文>=yw1b to ywb1
COUNT for 语文>=yw2b .and. 语文<yw1b to ywb2
COUNT for 语文>=yw2b to ywb3
COUNT for 数学>=sx1b to sxb1
COUNT for 数学>=sx2b .and. 数学<sx1b to sxb2
COUNT for 数学>=sx2b to sxb3
COUNT for 英语>=yy1b to yyb1
COUNT for 英语>=yy2b .and. 英语<yy1b to yyb2
COUNT for 英语>=yy2b to yyb3
COUNT for 物理>=wl1b to wlb1
COUNT for 物理>=wl2b .and. 物理<wl1b to wlb2
COUNT for 物理>=wl2b to wlb3
COUNT for 化学>=hx1b to hxb1
COUNT for 化学>=hx2b .and. 化学<hx1b to hxb2
COUNT for 化学>=hx2b to hxb3
COUNT for 生物>=sw1b to swb1
COUNT for 生物>=sw2b .and. 生物<sw1b to swb2
COUNT for 生物>=sw2b to swb3
COUNT for 总分>=zf1b to zfb1
COUNT for 总分>=zf2b .and. 总分<zf1b to zfb2
COUNT for 总分>=zf2b to zfb3
SELECT 2
GO bott
REPLACE 语文一本 with ywb1,语文二本 with ywb2,语文本科 with ywb3
REPLACE 数学一本 with sxb1,数学二本 with sxb2,数学本科 with sxb3
REPLACE 英语一本 with yyb1,英语二本 with yyb2,英语本科 with yyb3
REPLACE 物理一本 with wlb1,物理二本 with wlb2,物理本科 with wlb3
REPLACE 化学一本 with hxb1,化学二本 with hxb2,化学本科 with hxb3
REPLACE 生物一本 with swb1,生物二本 with swb2,生物本科 with swb3
REPLACE 总分一本 with zfb1,总分二本 with zfb2,总分本科 with zfb3
CLOSE TABLES
MESSAGEBOX("计算成功！")
2020-01-22 09:39
2020-04-22 16:38