挑战高手,我揣摩好久了搞不定,恳请指教啊???? 50limaowa]

发布时间:2016-12-6 16:17:00 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"挑战高手,我揣摩好久了搞不定,恳请指教啊???? 50limaowa]",主要涉及到挑战高手,我揣摩好久了搞不定,恳请指教啊???? 50limaowa]方面的内容,对于挑战高手,我揣摩好久了搞不定,恳请指教啊???? 50limaowa]感兴趣的同学可以参考一下。

题目较长,恳请帮助 有成绩表T_Score 缺考情况不录入,例如B001的L002课程缺考。 Stu_id(学生号) Lession_id(课程) Score(成绩) A001 L001 90 A001 L002 80 A002 L001 70 A002 L002 60 B001 L001 50 B002 L001 85 …… …… …… 学生档案T_Stu_Profile 包含所有学生信息 Stu_id(学生号) Stu_name(姓名) Class_id(班级) A001 张三 06101 A002 李四 06101 B001 王五 06102 …… …… …… 课程信息表T_Lession 包含所有课程信息 Lession_id(课程号) Lession_des(课程) L001 语文 L002 数学 L003 英语 L004 物理 L005 化学 1、 找出缺考的学生名单,输出如下格式: Class_id(班级) Stu_name(姓名) Lession_des(课程) B001          王五           数学 …… …… …… 2、 找出五门课程中的年级前三名,输出如下格式: Lession_des(课程) 第一名 第二名 第三名 语文 数学 英语 物理 化学 …… …… 3、 输出06101班的学生成绩单,格式如下: 姓名 语文 数学 英语 物理 化学 总分 …… 4、 假如成绩表增加考试日期Test_Date,记录高中三年大大小小每次考试成绩。 请问:如何求出高三阶段(2005年),每位学生的每门课的平均考试成绩。(缺考以及60分以下的成绩不计入  如高三数学共考试20次 B001缺考一次、另一次成绩58分,则B001的平均考试成绩以18次计算)。输出格式同试题三。

练习题啊。 做做看

谢谢,那我再仔细研究下     您是我们(fenet)公司的前辈吗

1. select b.class_id,b.stu_name,c.lession_des --class_id,stu_name,lession_des from (select distinct t.stu_id,s.lession_id from t_score s,      (select stu_id,lession_id=max(lession_id)  from t_score  group by stu_id  having count(*)<>(select count(distinct lession_id) from t_score)) t where s.lession_id<>t.lession_id) a left join t_stu_profile b on a.stu_id=b.stu_id left join t_lession c on a.lession_id=c.lession_id

我仔细消化一下,还没有解决过这么复杂的语句呢!! 谢谢您哈    不早了,寝室要熄灯了,我明天再来看哦 好象您以前就给过我不少帮助的哦

3、 输出06101班的学生成绩单,格式如下: 姓名 语文 数学 英语 物理 化学 总分 */ if exists(select 1 from sysobjects where id=object_id(N'v_t') and xtype=N'V') drop view v_t go create view v_t as select a.stu_id,a.stu_name,a.class_id,b.lession_id,b.score,c.lession_des --class_id,stu_name,lession_des from t_stu_profile a  left join t_score b on a.stu_id=b.stu_id left join t_lession c on b.lession_id=c.lession_id declare @s varchar(8000) set @s='' select @[email protected]+',['+lession_des+']=isnull(max(case when lession_des= ''' + lession_des +''' then score end),0) ' from v_t where class_id='06101' group by lession_des  set @s='select 姓名=stu_name [email protected]+' from v_t where class_id=''06101'' group by stu_name' exec(@s) /* 姓名         数学          语文           ---------- ----------- -----------  李四         60          70 张三         80          90 */

少了个总分,必下 create table T_Score(Stu_id varchar(4),Lession_id varchar(4),Score int) insert t_score select 'A001','L001',90 union all select 'A001','L002',80 union all select 'A002','L001',70 union all select 'A002','L002',60 union all select 'B001','L001',50 union all select 'B002','L001',85 create table T_Stu_Profile(Stu_id varchar(4),Stu_name varchar(10),Class_id varchar(5)) insert t_stu_profile select 'A001','张三','06101' union all select 'A002','李四','06101' union all select 'B001','王五','06102' create table T_Lession(Lession_id varchar(4),Lession_des varchar(10)) insert t_lession select 'L001','语文' union all select 'L002','数学' union all select 'L003','英语' union all select 'L004','物理' union all select 'L005','化学' if exists(select 1 from sysobjects where id=object_id(N'v_t') and xtype=N'V') drop view v_t go create view v_t as select a.stu_id,a.stu_name,a.class_id,b.lession_id,b.score,c.lession_des --class_id,stu_name,lession_des from t_stu_profile a  left join t_score b on a.stu_id=b.stu_id left join t_lession c on b.lession_id=c.lession_id declare @s varchar(8000) set @s='' select @[email protected]+',['+lession_des+']=isnull(max(case when lession_des= ''' + lession_des +''' then score end),0) ' from v_t where class_id='06101' group by lession_des  set @s='select 姓名=stu_name [email protected]+',总分=sum(score) from v_t where class_id=''06101'' group by stu_name' --print @s exec(@s) /* 姓名         数学          语文          总分           ---------- ----------- ----------- -----------  李四         60          70          130 张三         80          90          170 */

/*4、 假如成绩表增加考试日期Test_Date,记录高中三年大大小小每次考试成绩。 请问:如何求出高三阶段(2005年),每位学生的每门课的平均考试成绩。(缺考以及60分以下的成绩不计入  如高三数学共考试20次 B001缺考一次、另一次成绩58分,则B001的平均考试成绩以18次计算)。输出格式同试题三。 */ create table T_Score(Stu_id varchar(4),Lession_id varchar(4),Score int,test_date datetime) insert t_score select 'A001','L001',90,'2005-12-01' union all select 'A001','L002',80,'2005-12-01' union all select 'A002','L001',70,'2005-12-01' union all select 'A002','L002',60,'2005-12-01' union all select 'A001','L001',90,'2005-08-01' union all select 'A001','L002',80,'2005-08-01' union all select 'A002','L001',70,'2005-08-01' union all select 'A002','L002',40,'2005-08-01' union all select 'A001','L001',90,'2005-04-01' union all select 'A001','L002',50,'2005-04-01' union all select 'A002','L001',70,'2005-04-01' union all select 'A002','L002',60,'2005-04-01' union all select 'B001','L001',50,'2005-12-01' union all select 'B002','L001',85 ,'2005-12-01' if exists(select 1 from sysobjects where id=object_id(N'v_t') and xtype=N'V') drop view v_t go create view v_t as select a.stu_id,a.stu_name,a.class_id,b.lession_id,b.score,c.lession_des,b.test_date --class_id,stu_name,lession_des from t_stu_profile a  left join t_score b on a.stu_id=b.stu_id left join t_lession c on b.lession_id=c.lession_id where year(b.test_date)=2005 and b.score>=60 declare @s varchar(8000) set @s='' select @[email protected]+',['+lession_des+']=isnull(avg(case when lession_des= ''' + lession_des +''' then score end),0) ' from v_t where class_id='06101' group by lession_des  set @s='select 姓名=stu_name [email protected]+',总平均分=sum(score)/count(*) from v_t where class_id=''06101'' and year(test_date)=2005 and score>=60  group by stu_name' --print @s exec(@s) /* 姓名         数学          语文          总平均分         ---------- ----------- ----------- -----------  李四         60          70          65 张三         80          90          85 */

来晚了,都写对了呀

第4题的总分不知道怎么算,所以只算了总平均分,哪位看看

/* 2、找出五门课程中的年级前三名,输出如下格式: Lession_des(课程)第一名第二名第三名 语文 数学 英语 物理 化学………… */ if exists(select 1 from sysobjects where id=object_id(N'v_t') and xtype=N'V') drop view v_t go create view v_t as select a.stu_id,a.stu_name,a.class_id,b.lession_id,b.score,c.lession_des --class_id,stu_name,lession_des from t_stu_profile a  left join t_score b on a.stu_id=b.stu_id left join t_lession c on b.lession_id=c.lession_id select distinct 班级=a.class_id, 课程=a.lession_des, 第一名=(select top 1 stu_name from v_t where class_id=a.class_id and lession_des=a.lession_des order by score desc), 第二名=(select top 1 b.stu_name from (select top 2 stu_name,score from v_t where class_id=a.class_id and lession_des=a.lession_des order by score desc) b order by b.score), 第三名=(select top 1 c.stu_name from (select top 3 stu_name,score from v_t where class_id=a.class_id and lession_des=a.lession_des order by score desc) c order by c.score) from v_t a /* 班级    课程         第一名        第二名        第三名         ----- ---------- ---------- ---------- ----------  06101 数学         张三         李四         李四 06101 语文         张三         李四         李四 06102 语文         王五         王五         王五 */

真的非常感谢您的关注:   我从早上开始看第一题,一直研究到现在,觉得我跟您相比差距实在是太大了,以后一定多向您请教,其他的还没有看懂,只发现第一题还是有一点小问题,就是 select stu_id,lession_id=max(lession_id   取出来的是最大值,也就是只有一个值 经过后面的   where s.lession_id<>t.lession_id)  结果得到了所以比 max小的都有了  例如      A002 考了 L001(语文)但是还是作为了结果的一部分的 再麻烦您帮修改下哈  谢谢哦

--Stu_id(学生号) Lession_id(课程) Score(成绩) create table ##score (stu_id varchar(100),Lession_id varchar(200) ,Score dec(10,2)) insert into ##score select  'A001', 'L001', 90 union Select  'A001' ,'L002',80 union Select  'A002' ,'L001',70 union Select  'A002' ,'L002',60 union Select  'B001' ,'L001',50 union Select  'B002', 'L001',85 --Stu_id(学生号) Stu_name(姓名) Class_id(班级) Create table ##Stu_Profile(stu_id varchar(100),Stu_name varchar(200) ,Class_id varchar(200)) insert into ##Stu_Profile Select 'A001' ,'张三','06101' union Select  'A002', '李四', '06101' union Select  'B001', '王五', '06102' union Select  'B002', '王六', '06102' Create table ##T_Lession(Lession_id varchar(100),Lession_des varchar(200)) --Lession_id(课程号) Lession_des(课程) insert into ##T_Lession select  'L001','语文' union Select  'L002', '数学' union Select  'L003', '英语' union Select  'L004', '物理' union Select  'L005','化学' --1##score select A.Lession_des,b.Stu_Name from  ##T_Lession A , ##Stu_Profile B where A.Lession_id not in(Select Lession_id from ##score C where C.Stu_Id=b.Stu_Id and A.Lession_id=C.Lession_id) --2 Select Lession_des ,Stu_Name,Score,Class_id from  (Select C.Stu_Name,Lession_des,Score,Class_id from ##T_Lession A,##score b,##Stu_Profile c Where A.Lession_id =b.Lession_Id and b.Stu_id=c.stu_id and  b.score>= (Select min(Score) from  (select Top 3 Score from ##score where A.Lession_id=Lession_Id and Stu_id=C.Stu_id order by Score desc) D) ) AA  order by Lession_des,Class_id ,Score desc --3 declare @Sql varChar(8000) declare @Sum1 varChar(8000) declare @Sum2 varChar(8000) set @Sql='Select Stu_Name as 姓名,' set @Sum1='' set @Sum2='' Select @[email protected]+'case Lession_id when '''+Lession_id+''' then  isNUll(score,0) End as '+Lession_des+',',@[email protected]+'Sum(isNull('+Lession_des+',0)) as '+Lession_des+',',@[email protected]+lession_des+'+' from ##T_Lession set @sql=stuff(@sql,len(@sql),1,'') set @Sum1=stuff(@Sum1,len(@Sum1),1,'') set @Sum2=stuff(@Sum2,len(@Sum2),1,'')+' as 总分' set @[email protected]+' from ##Score A join ##Stu_Profile b on a.Stu_id=b.Stu_id ' Set @Sql='Select 姓名,[email protected]+' from (' [email protected]+')AA group by 姓名 ' Set @Sql='Select *,[email protected]+' from (' [email protected]+')BB' print @Sql exec(@sql) --4 --从上面找,加个日期数据

只发现第一题还是有一点小问题,就是 select stu_id,lession_id=max(lession_id   取出来的是最大值,也就是只有一个值 经过后面的   where s.lession_id<>t.lession_id)  结果得到了所以比 max小的都有了  例如      A002 考了 L001(语文)但是还是作为了结果的一部分的 --------- 楼主你运行整个1的语句没?根据我上面表格的数据应该查出“王五”的“数学”没考,还有一个你的学生表里没记录的人没考“数学”,不过别名为a的select语句我也觉得要改改 思路是: 1.根据T_score表确定已经考了几门,不可能没有考试过的科目,也算人家缺考吧;   已经考了的科目数=select count(distinct lession_id) from t_score 2。再找出缺考的人(本来只选了一个stu_id的,后来想到要取出lession_id作比较,所以加了lession_id=max(lession_id),这里是有一些问题,然后得出缺考人的ID和缺考科目, 下面是别名a的改正(楼主你再试试): select t.stu_id,s.lession_id from    (select distinct lession_id from t_score) s ,   (select stu_id,lession_id     from t_score where stu_id in    (select stu_id      from t_score      group by stu_id     having count(*)<>(select count(distinct lession_id) from t_score)    )) t where exists(select 1 from T_score where s.lession_id<>t.lession_id) 3。求出没考的科目后,接合其他表生成结果集。

1、找出缺考的学生名单,输出如下格式: select b.class_id,b.stu_name,c.lession_des  from (select t.stu_id,s.lession_id       from    (select distinct lession_id from t_score) s ,   (select stu_id,lession_id     from t_score where stu_id in    (select stu_id      from t_score      group by stu_id     having count(*)<>(select count(distinct lession_id) from t_score)    )) t   where s.lession_id<>t.lession_id ) a left join t_stu_profile b on a.stu_id=b.stu_id left join t_lession c on a.lession_id=c.lession_id

1、找出缺考的学生名单,输出如下格式 ------------------------------------------------- 不过还有种情况没考虑到某人一次都没考,就会出问题了,看来要从T_score表找出学生对应的班级,再通过T_stu_profile来确定所有要考的人,再比较T_score的记录才能得出结果。

看着您在一天之内给我做出了这么多的答案,真的是再怎么表达我的感激之请都不过分。。。。真有一种拜您为师的冲动,太佩服您了,肯收我这个小徒弟吗??    下午有学校有课,没有时间上网来看,我提出第一个问题的时候是运行过了,我再运行下您的修改过的答案    有了结果我给您说哈    再次谢谢     

06101 张三三 数学       06101 张三三 英语       06101 张三三 物理       06101 张三三 化学       06101 张三三 语文       06101 张三三 英语       06101 张三三 物理       06101 张三三 化学       06101 李四四 数学       06101 李四四 英语       06101 李四四 物理       06101 李四四 化学       06101 李四 数学       06101 李四 英语       06101 李四 物理       06101 李四 化学       06102 王五 数学       06102 王五 英语       06102 王五 物理       06102 王五 化学       06102 王五五 数学       06102 王五五 英语       06102 王五五 物理       06102 王五五 化学       我运行了这是您在   2005-12-30 16:25:00 时间里的解法的结果,看来还是有点问题哦,我们一起找出错的地方啊

也非常感谢hexianhua2000(Best One) 对我问题给予的关注 而且  我发现  hexianhua2000(Best One) 给出的至少关于第一踢的答案是正确的{其他的还没有运行,因为这个也没有完全搞懂呢}    我再琢磨下哈!!    谢谢哦!!

--Stu_id(学生号) Lession_id(课程) Score(成绩) create table ##score (stu_id varchar(100),Lession_id varchar(200) ,Score dec(10,2),test_date varchar(19)) insert into ##score select  'A001', 'L001', 90,'2005-12-31' union Select  'A001' ,'L002',80 ,'2005-12-31'  union Select  'A002' ,'L001',70, '2005-12-31' union Select  'A002' ,'L002',60, '2005-12-31' union Select  'B001' ,'L001',50, '2005-12-31' union Select  'B002', 'L001',85, '2005-12-31' --Stu_id(学生号) Stu_name(姓名) Class_id(班级) Create table ##Stu_Profile(stu_id varchar(100),Stu_name varchar(200) ,Class_id varchar(200)) insert into ##Stu_Profile Select 'A001' ,'张三','06101' union Select  'A002', '李四', '06101' union Select  'B001', '王五', '06102' union Select  'B002', '王六', '06102' Create table ##T_Lession(Lession_id varchar(100),Lession_des varchar(200)) --Lession_id(课程号) Lession_des(课程) insert into ##T_Lession select  'L001','语文' union Select  'L002', '数学' union Select  'L003', '英语' union Select  'L004', '物理' union Select  'L005','化学' --1##score select A.Lession_des,b.Stu_Name from  ##T_Lession A , ##Stu_Profile B where A.Lession_id not in(Select Lession_id from ##score C where C.Stu_Id=b.Stu_Id and A.Lession_id=C.Lession_id) --2 Select Lession_des ,Stu_Name,Score,Class_id from  (Select C.Stu_Name,Lession_des,Score,Class_id from ##T_Lession A,##score b,##Stu_Profile c Where A.Lession_id =b.Lession_Id and b.Stu_id=c.stu_id and  b.score>= (Select min(Score) from  (select Top 3 Score from ##score where A.Lession_id=Lession_Id and Stu_id=C.Stu_id order by Score desc) D) ) AA  order by Lession_des,Class_id ,Score desc --3 declare @Sql varChar(8000) declare @Sum1 varChar(8000) declare @Sum2 varChar(8000) declare @Con varChar(8000) set @Sql='Select Stu_Name as 姓名,test_date ,' set @Sum1='' set @Sum2='' set @Con='' Select @[email protected]+'case Lession_id when '''+Lession_id+''' then  isNUll(score,0) End as ['+Lession_des+'],' ,@[email protected]+'Sum(isNull(['+Lession_des+'],0)) as ['+Lession_des+'],', @[email protected]+'['+lession_des+']+', @[email protected]+'['+lession_des+']>=60 Or ' from ##T_Lession set @sql=stuff(@sql,len(@sql),1,'') set @Sum1=stuff(@Sum1,len(@Sum1),1,'') set @Sum2=stuff(@Sum2,len(@Sum2),1,'')+' as [总分]' set @[email protected]+' from ##Score A join ##Stu_Profile b on a.Stu_id=b.Stu_id ' Set @Sql='Select 姓名,[email protected]+',test_date=year(test_date) from (' [email protected]+')AA group by 姓名 ,year(test_date)' Set @Sql='Select *,[email protected]+'  from (' [email protected]+')BB' print @Sql exec(@sql) --4  Set @Sum1=replace(@Sum1,'Sum','AVG')   set @Con='where ('+stuff(@Con,len(@Con)-2,3,'')+') and test_date='+'2005' print @Con Set @Sql='Select 姓名,[email protected]+',AVG(总分) as 平均总分 from ([email protected]+') CC [email protected]+' group by 姓名 '  print @Sql exec(@sql) drop table ##Score drop table ##Stu_Profile drop table ##T_Lession

非常感谢。。。。 认真领悟中。。。。。。。

基本已经弄清楚了高手的解答,我非常感谢!    由于ERP和其他几门考试这周进行,所涉及的几个较深的知识点还没有完全掌握,我先把帖子结了,以后再问各位好吗?    新年快乐,工作顺利哦!!

上一篇:why "Eve can retrieve data from the sales.order table by executing sales.getorders"? 20CSDN]
下一篇:SQL的一个表里最多可以存多少条纪录? and 谁有SQL高手群??? 5CSDN]

相关文章

相关评论