绝顶高手们过来！一个小小挑战交给您了！

caina3 2005-09-26 11:43:00

对我来说是绝对的难题，只好交给您帮我看看了。
哭求。谢谢！分一定给！

需求如下：
有这样一张表：dmstm
stno stsubject sttype styear stpd ststm

9990101 地理期末 20042005 第一学期 75
9990101 化学期末 20042005 第一学期 87
9990101 生物期末 20042005 第一学期 85.5
9990101 数学期末 20042005 第一学期 85
9990101 外语期末 20042005 第一学期 70
9990101 物理期末 20042005 第一学期 86
9990101 语文期末 20042005 第一学期 64
9990101 政治期末 20042005 第一学期 48
9990101 计算机原理期末 20042005 第一学期 70
9990101 C语言编程期末 20042005 第一学期 83
9990101 basic语言期末 20042005 第一学期 64
9990101 AAAA 期末 20042005 第一学期 48
9990101 BBBBBB 期末 20042005 第一学期 70
9990101 DDDDDD 期末 20042005 第一学期 83

9990101 地理期中 20042005 第一学期 75
9990101 化学期中 20042005 第一学期 87
9990101 生物期中 20042005 第一学期 85.5
9990101 数学期中 20042005 第一学期 85
9990101 外语期中 20042005 第一学期 70
9990101 物理期中 20042005 第一学期 86
9990101 语文期中 20042005 第一学期 64
9990101 政治期中 20042005 第一学期 48
9990101 计算机原理期中 20042005 第一学期 70
9990101 C语言编程期中 20042005 第一学期 83
9990101 basic语言期中 20042005 第一学期 64
9990101 AAAA 期中 20042005 第一学期 48
9990101 BBBBBB 期中 20042005 第一学期 70
9990101 DDDDDD 期中 20042005 第一学期 83

9990101 地理测验1 20042005 第一学期 75
9990101 化学测验1 20042005 第一学期 87
9990101 生物测验1 20042005 第一学期 85.5
9990101 数学测验1 20042005 第一学期 85
9990101 外语测验1 20042005 第一学期 70
9990101 物理测验1 20042005 第一学期 86
9990101 语文测验1 20042005 第一学期 64
9990101 政治测验1 20042005 第一学期 48
9990101 计算机原理测验1 20042005 第一学期 70
9990101 C语言编程测验1 20042005 第一学期 83
9990101 basic语言测验1 20042005 第一学期 64
9990101 AAAA 测验1 20042005 第一学期 48
9990101 BBBBBB 测验1 20042005 第一学期 70
9990101 DDDDDD 测验1 20042005 第一学期 83

9990101 地理测验2 20042005 第一学期 75
9990101 化学测验2 20042005 第一学期 87
9990101 生物测验2 20042005 第一学期 85.5
9990101 数学测验2 20042005 第一学期 85
9990101 外语测验2 20042005 第一学期 70
9990101 物理测验2 20042005 第一学期 86
9990101 语文测验2 20042005 第一学期 64
9990101 政治测验2 20042005 第一学期 48
9990101 计算机原理测验2 20042005 第一学期 70
9990101 C语言编程测验2 20042005 第一学期 83
9990101 basic语言测验2 20042005 第一学期 64
9990101 AAAA 测验2 20042005 第一学期 48
9990101 BBBBBB 测验2 20042005 第一学期 70
9990101 DDDDDD 测验2 20042005 第一学期 83

9990101 地理测验3 20042005 第一学期 75
9990101 化学测验3 20042005 第一学期 87
9990101 生物测验3 20042005 第一学期 85.5
9990101 数学测验3 20042005 第一学期 85
9990101 外语测验3 20042005 第一学期 70
9990101 物理测验3 20042005 第一学期 86
9990101 语文测验3 20042005 第一学期 64
9990101 政治测验3 20042005 第一学期 48
9990101 计算机原理测验3 20042005 第一学期 70
9990101 C语言编程测验3 20042005 第一学期 83
9990101 basic语言测验3 20042005 第一学期 64
9990101 AAAA 测验3 20042005 第一学期 48
9990101 BBBBBB 测验3 20042005 第一学期 70
9990101 DDDDDD 测验3 20042005 第一学期 83

9990101 地理期末 20042005 第二学期 75
9990101 化学期末 20042005 第二学期 87
9990101 生物期末 20042005 第二学期 85.5
9990101 数学期末 20042005 第二学期 85
9990101 外语期末 20042005 第二学期 70
9990101 物理期末 20042005 第二学期 86
9990101 语文期末 20042005 第二学期 64
9990101 政治期末 20042005 第二学期 48
9990101 计算机原理期末 20042005 第二学期 70
9990101 C语言编程期末 20042005 第二学期 83
9990101 basic语言期末 20042005 第二学期 64
9990101 AAAA 期末 20042005 第二学期 48
9990101 BBBBBB 期末 20042005 第二学期 70
9990101 DDDDDD 期末 20042005 第二学期 83

9990101 地理期中 20042005 第二学期 75
9990101 化学期中 20042005 第二学期 87
9990101 生物期中 20042005 第二学期 85.5
9990101 数学期中 20042005 第二学期 85
9990101 外语期中 20042005 第二学期 70
9990101 物理期中 20042005 第二学期 86
9990101 语文期中 20042005 第二学期 64
9990101 政治期中 20042005 第二学期 48
9990101 计算机原理期中 20042005 第二学期 70
9990101 C语言编程期中 20042005 第二学期 83
9990101 basic语言期中 20042005 第二学期 64
9990101 AAAA 期中 20042005 第二学期 48
9990101 BBBBBB 期中 20042005 第二学期 70
9990101 DDDDDD 期中 20042005 第二学期 83

9990101 地理测验1 20042005 第二学期 75
9990101 化学测验1 20042005 第二学期 87
9990101 生物测验1 20042005 第二学期 85.5
9990101 数学测验1 20042005 第二学期 85
9990101 外语测验1 20042005 第二学期 70
9990101 物理测验1 20042005 第二学期 86
9990101 语文测验1 20042005 第二学期 64
9990101 政治测验1 20042005 第二学期 48
9990101 计算机原理测验1 20042005 第二学期 70
9990101 C语言编程测验1 20042005 第二学期 83
9990101 basic语言测验1 20042005 第二学期 64
9990101 AAAA 测验1 20042005 第二学期 48
9990101 BBBBBB 测验1 20042005 第二学期 70
9990101 DDDDDD 测验1 20042005 第二学期 83

9990101 地理测验2 20042005 第二学期 75
9990101 化学测验2 20042005 第二学期 87
9990101 生物测验2 20042005 第二学期 85.5
9990101 数学测验2 20042005 第二学期 85
9990101 外语测验2 20042005 第二学期 70
9990101 物理测验2 20042005 第二学期 86
9990101 语文测验2 20042005 第二学期 64
9990101 政治测验2 20042005 第二学期 48
9990101 计算机原理测验2 20042005 第二学期 70
9990101 C语言编程测验2 20042005 第二学期 83
9990101 basic语言测验2 20042005 第二学期 64
9990101 AAAA 测验2 20042005 第二学期 48
9990101 BBBBBB 测验2 20042005 第二学期 70
9990101 DDDDDD 测验2 20042005 第二学期 83

9990101 地理测验3 20042005 第二学期 75
9990101 化学测验3 20042005 第二学期 87
9990101 生物测验3 20042005 第二学期 85.5
9990101 数学测验3 20042005 第二学期 85
9990101 外语测验3 20042005 第二学期 70
9990101 物理测验3 20042005 第二学期 86
9990101 语文测验3 20042005 第二学期 64
9990101 政治测验3 20042005 第二学期 48
9990101 计算机原理测验3 20042005 第二学期 70
9990101 C语言编程测验3 20042005 第二学期 83
9990101 basic语言测验3 20042005 第二学期 64
9990101 AAAA 测验3 20042005 第二学期 48
9990101 BBBBBB 测验3 20042005 第二学期 70
9990101 DDDDDD 测验3 20042005 第二学期 83

需获得如下数据：

stno 地理1 地理2 地理化学1 化学2 化学生物1 生物2 生物
数学1 数学2 数学外语1 外语2 外语物理1 物理2 物理语文1 语文2 语文
政治1 政治2 政治计算机原理1 计算机原理2 计算机 C语言编程1 C语言编程2 C语言编程
basic语言1 basic语言2 basic语言 AAAA1 AAAA2 AAAA BBBBBB1 BBBBBB2 BBBBBB
DDDDDD1 DDDDDD2 DDDDDD3

必要说明：
dmstm中：
stno：学生编号
stsubject：科目
sttype：考试类型
styear：考试学年
stpd：考试学期
ststm：考试成绩

新出来的表中：
stno：学生编号
地理1：地理科目20042005学年第一学期总评成绩
地理2：地理科目20042005学年第二学期总评成绩
地理：地理科目20042005学年总评成绩
其余类似

其中：学期总评成绩=（（学期测验1+学期测验2+学期测验3）/学期测验次数）*0.3+学期期中*0.3+学期期末*0.4
学年总评成绩=第一学期学期总评成绩*0.4+第二学期学期总评成绩*0.6

备注:
1.事先未知有多少个科目
2.事先未知有多少次测验
3.事先未知某一种测验类型是否有成绩(记录)

...全文

163 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

caina3 2005-10-08

打赏
举报

没意思。
没有一个正确答案，
却又不好意思不给分。
-_-!

caina3 2005-09-27

打赏
举报

没有人给个正确的答案么？

云中客 2005-09-26

打赏
举报

vivianfdlpw 佩服

IvyAngta 2005-09-26

打赏
举报

vivianfdlpw() 我太佩服你了！！！！！你就是老大！
老大我佩服你！

vivianfdlpw 2005-09-26

打赏
举报

create table dmstm
(
stno varchar(20),
stsubject varchar(20),
sttype varchar(10),
styear varchar(20),
stpd varchar(10),
ststm decimal(12,2)
)
--部分数据
insert dmstm
select '9990101','地理','期末','20042005','第一学期','75' union
select '9990101','化学','期末','20042005','第一学期','87' union
select '9990101','生物','期末','20042005','第一学期','85.5' union
select '9990101','数学','期末','20042005','第一学期','85' union
select '9990101','外语','期末','20042005','第一学期','70' union
select '9990101','物理','期末','20042005','第一学期','86' union
select '9990101','语文','期末','20042005','第一学期','64' union
select '9990101','政治','期末','20042005','第一学期','48' union
select '9990101','计算机原理','期末','20042005','第一学期','70' union
select '9990101','C语言编程','期末','20042005','第一学期','75'

insert dmstm
select '9990101','地理','期末','20042005','第二学期','75' union
select '9990101','化学','期末','20042005','第二学期','87' union
select '9990101','生物','期末','20042005','第二学期','85.5' union
select '9990101','数学','期末','20042005','第二学期','85' union
select '9990101','外语','期末','20042005','第二学期','70' union
select '9990101','物理','期末','20042005','第二学期','86' union
select '9990101','语文','期末','20042005','第二学期','64' union
select '9990101','政治','期末','20042005','第二学期','48' union
select '9990101','计算机原理','期末','20042005','第二学期','70' union
select '9990101','C语言编程','期末','20042005','第二学期','75'

--查询
declare @sql varchar(8000)
select @sql=''

select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from dmstm group by stsubject
select @sql='select stno'+@sql+' from dmstm group by stno'
exec(@sql)

--删除测试环境
drop table dmstm

--结果
/*
stno C语言编程1 C语言编程2 C语言编程地理1 地理2 地理化学1 化学2 化学计算机原理1 计算机原理2 计算机原理生物1 生物2 生物数学1 数学2 数学外语1 外语2 外语物理1 物理2 物理语文1 语文2 语文政治1 政治2 政治
-------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
9990101 75.00 75.00 150.00 75.00 75.00 150.00 87.00 87.00 174.00 70.00 70.00 140.00 85.50 85.50 171.00 85.00 85.00 170.00 70.00 70.00 140.00 86.00 86.00 172.00 64.00 64.00 128.00 48.00 48.00 96.00

*/

vivianfdlpw 2005-09-26

打赏
举报

declare @sql varchar(8000)
select @sql=''

select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from dmstm group by stsubject
select @sql='select stno'+@sql+' from dmstm group by stno'
exec(@sql)

vivianfdlpw 2005-09-26

打赏
举报

declare @sql varchar(8000)
select @sql=''

select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from 表 group by stsubject
select @sql='select stno'+@sql+' from 表 group by stno'
exec(@sql)

churchatp1 2005-09-26

打赏
举报

备注
1。2。3。动态sql

caina3 2005-09-26

打赏
举报

上面备注中，第3项为，事先未知某一种考试类型是否有成绩（记录）

caina3 2005-09-26

打赏
举报

需求太复杂。。。。。。俺只能陈述到这个样子了。
哎~！感觉好难啊。。。。。。哭了。

vivianfdlpw 2005-09-26

打赏
举报

多个varchar(8000)组合
参看
http://community.csdn.net/Expert/topic/4143/4143434.xml?temp=.2563898

Oliko 2005-09-26

打赏
举报

看上去复杂。把每期的结果再union 不就可以了？

子陌红尘 2005-09-26

打赏
举报

展开的sql语句的长度好象会超过8000吧？
-------------------------------------------------------------------------
可以用多个varchar(8000)的字符串变量分别拼装SQL语句的一部分，最后
exec @var1+@var2+@var3+...+@varn

wscft 2005-09-26

打赏
举报

靠，挺复杂的！

caina3 2005-09-26

打赏
举报

如果仅仅是那么简单的话我也不用在标题上写"绝顶"高手来了。
而且这里面还要注意的是：

如果某一年的学科为14科，
而每一个学期每一门课的平时的测验次数超过3次（我们假象为5次吧），
这个时候
展开的sql语句的长度好象会超过8000吧？

caina3 2005-09-26

打赏
举报

我在写问题的时候，把dmstm表中的记录分段来写了的。dmstm表中不仅仅有期末的成绩，还有期中的成绩和平时测验的成绩，而且，平时测验有可能有多个测验。

每个学期的总评成绩=这个学期的测验的平均值*0.3+这个学期的期中成绩*0.3+这个学期的期末的成绩*0.4
然后学年总评的成绩=第一学期的学期的总评成绩*0.4+第二学期的学期的总评成绩*0.6
................................................

caina3 2005-09-26