请各位看看我的表结构是不是合理！请一sql语句

[iamgsyy]

本人在做一个成绩管理系统！

表结构：

courses---课程表
course_no
course_name
course_type
course_point (学分)

score---成绩表
id
stu_no (学号)
course_no (课程号)
score (成绩)
term (学期)

现在要求综合考评，综合考评的公式是
（课程1成绩*课程1学分+课程2成绩*课程2学分+....）/（课程1学分+课程2学分+...）

要求算出综合成绩，并排序！

[冷箫轻笛]

select t1.term,t1.stu_no,综合成绩 = (sum(t1.score * t2.course_point)/sum(t2.course_point))
from score t1,courses t2
where t1.course_no =t2.course_no
order by 综合成绩 desc

[冷箫轻笛]

--修改一下！
select t1.term,t1.stu_no,综合成绩 = (sum(t1.score * t2.course_point)/sum(t2.course_point))
from score t1,courses t2
where t1.course_no =t2.course_no
group by t1.term,t1.stu_no
order by 综合成绩 desc

[子陌红尘]

select
s.stu_no,
sum(s.score*c.course_point)*1.0/sum(c.course_point) as 综合成绩
from
courses c,
score s
where
c.course_no=s.course_no
group by
s.stu_no
order by
综合成绩 desc

[xeqtr1982]

declare @t table(course_no int,course_name varchar(10),course_type varchar(10),course_point int)
insert into @t select 1,'语文','a',4
insert into @t select 2,'数学','a',3
insert into @t select 3,'英语','a',2
insert into @t select 4,'政治','a',1

declare @a table(id int,stu_no int,course_no int,score int,term varchar(10))
insert into @a select 1,1,1,100,'06上'
insert into @a select 2,1,2,78,'06上'
insert into @a select 3,1,3,79,'06上'
insert into @a select 4,2,1,90,'06上'
insert into @a select 5,2,2,74,'06上'
insert into @a select 6,2,3,95,'06上'

select term,
stu_no,
综合成绩=cast((sum(b.score*a.course_point)+0.0)/sum(a.course_point) as dec(10,1))
from @t a,@a b
where a.course_no=b.course_no
group by term,stu_no
order by cast((sum(b.score*a.course_point)+0.0)/sum(a.course_point) as dec(10,1)) desc

[wangwei155]

路过
帮顶

[iamgsyy]

大家看看这样对不对？
Select stu_no,Sum(score * course_point)/Sum(course_point) as 综合考评,
(sum(case course_type when 'other' then score else 0 end)) as 道德
from score a Join courses b On a.course_id = b.course_no
and a.course_id in (2,3,4)
and stu_no in (200281010119,200281010120,200281010121)
group by stu_no
order by 综合考评

[iamgsyy]

Select stu_no,Sum(score * course_point)/Sum(course_point) as 成绩,
sum(case course_type when 'other' then score*0.1 else 0 end) as 道德,
Sum(score * course_point)/Sum(course_point)+sum(case course_type when 'other' then score*0.1 else 0 end) as 综合考评
from score a Join courses b a.course_id = b.course_no and a.course_id in (2,3,4,10,11,12) and stu_no in (200281010119,200281010120,200281010121) group by stu_no order by 综合考评

终于好了

[zxbyhcsdn]

这又何难

[笨笨兔兔兔兔兔]

......show一下我在sql区的级别,呵呵

[冷箫轻笛]

现在是成绩表（courses）里的course_type字段是“other”时说明这个课程是道德分，
道德分的算法就是直接加了。而不用除学分了。

select t1.term,t1.stu_no,
道德成绩 = (sum(case t2.course_type when 'other' then t1.score * t2.course_point else 0 end))
from score t1,courses t2
where t1.course_no =t2.course_no
order by 道德成绩 desc