我有三张表结构如下:
score(成绩表) 字段:userid(学号) username(姓名) testid(试卷编号) score(成绩)
testuser(学生考试状态表) 字段:userid(学号) testid(试卷编号) havetest(0未考、1已考)
test(考试安排表) 字段: testid(试卷编号) starttime(考试时间YY/MM/DD)我要做个查询:
一、查询每个考生2006年的考试情况并将结果显示如下表格中:
姓名 学号 应考试次数 已考试次数 未考试次数请各位大侠帮看一下,这种多表查询我实在是做不来了!一定给分结贴!!
score(成绩表) 字段:userid(学号) username(姓名) testid(试卷编号) score(成绩)
testuser(学生考试状态表) 字段:userid(学号) testid(试卷编号) havetest(0未考、1已考)
test(考试安排表) 字段: testid(试卷编号) starttime(考试时间YY/MM/DD)我要做个查询:
一、查询每个考生2006年的考试情况并将结果显示如下表格中:
姓名 学号 应考试次数 已考试次数 未考试次数请各位大侠帮看一下,这种多表查询我实在是做不来了!一定给分结贴!!
from
(select userid,count(testid) as totaltest
from testuser
group by userid) total inner join
(select userid,count(testid) as havetest
from testuser
where havetest=1
group by userid) havetest on totaltest.userid=havetest.userid
select username ,Chengji.userid as userid,count(userid) as count1 ,testid
from score Chengji left join testuser Zhuangtai on(Chengji.userid = Zhuangtai.userid)
where Zhuangtai.havetest = 1 2,create view mei ( count2 ,testid, userid) as
select count(userid) as count2 ,testid,Chengji.userid as userid
from score Chengji left join testuser Zhuangtai on(Chengji.userid = Zhuangtai.userid)
where Zhuangtai.havetest = 0
3,select username as 姓名,userid as 学号,count1+count2 as 应考试次数,
count1 as 已考试次数 ,count2 as 未考试次数
from you A left join mei B
on(Yikao.testid = Weikao.testid and Yikao.userid = Weikao.userid )分别执行1,2,3部分,没调试过,思路是这样的,最好写成存储过程。。
select a.userid,a.username,count(b.havetest) as '应考次数',sum(b.havetest) as '已考试次数',count(b.havetest)-sum(b.havetest) as' 未考试次数'
from cadre_info a , exam_testuser b ,exam_test c
where a.userid=b.userid and year(c.settime)>='2005' and b.testid=c.testid
group by a.userid,a.username其中cadre_info 是用户表,里面用户及用户ID是唯一的,如果用exam_score的话(用户及ID是已考试次数)会造成重复统计,呵呵,对不起各位了,一张表用错让各们在里面多做许多!