2 my 00 my_00 于 2015.06.07 11:37 提问

求助!SQLServer查询实现由四张表得到新表?最新问题求助!!

有四张表:Course、CourseType、Grade、student

 表Course:
 courseID    coursename    coursetypeID
 100001        智育1               001
 100002        智育2               002
 200001        德育1               001
 200002        德育2               002
 300001        体育1               001
 300002        体育2               002
 表:CourseType:
 coursetypeID       typename
 001                         必修课
 002                         选修课
 表Grade:
 studentID    courseID    grade
  S1                 100001      80
    S1                 100002      80
    S1                 200001      80
    S1                 200002      80
    S1                 300001      80
    S1                 300002      80
    S2                 100001      70
    S2                 100002      70
    S2                 200001      70
    S2                 200002      70
    S2                 300001      70
    S2                 300002      70
表student:
studentID   studentName
S1                姓名1
S2                姓名2

我如何得到表:

studentID     studentName   CountZhiYu    CouuntDeYu     CountTiYu    Count
S1                  姓名1                 70%*160        20%*160            10%*160     160
S2                  姓名2                 70%*140        20%*140             10%*140     140


 其中:1.     160=80+80,140=70+70.即两个智育成绩相加。德育、体育成绩同理。
           2.      70%*160要总后计算结果。20%*160  、10%*160同理
                     3.      Count字段是前面三个数字字段的累加。
                              例:160=70%*160+20%*160+10%*160
                     4.       S1、S2的Count总分按照从大到小排序。

恳请各位行家高手不吝惜指教!万分感谢!!

7个回答

devmiao
devmiao   Ds   Rxr 2015.06.07 11:45

同样的道理,可以join,然后group by
解决了之前的问题后再写给你

my_00
my_00 你好。帮我看看好吗?谢谢啦。
2 年多之前 回复
my_00
my_00 谢谢。
2 年多之前 回复
danielinbiti
danielinbiti   Ds   Rxr 2015.06.07 15:28

SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count
from(
select studentID,studentName 
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1000%')*0.7 CountZhiYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '2000%')*0.2 CouuntDeYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '3000%')*0.1 CountTiYu
from student
) a
my_00
my_00 你好,你再帮我看看优化问题好吗?
2 年多之前 回复
my_00
my_00 需要优化的问题我已经回复了。
2 年多之前 回复
my_00
my_00 谢谢。我采纳你。我还有一点需要优化。你再帮我看看好吗?
2 年多之前 回复
my_00
my_00 需要改动原代码的那个地方?求赐教!
2 年多之前 回复
my_00
my_00 谢谢。得出的结果是表student里所有同学的成绩,所以没录入成绩的学生也显示成绩为0。但是我想只得到表Grade里所有已录入成绩的学生的成绩。
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: SQLServer的是ISNULL
2 年多之前 回复
my_00
my_00 有错误:'ifnull' 不是可以识别的 内置函数名称。
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: 如果没问题,别忘了顺手采纳一下
2 年多之前 回复
my_00
my_00 谢谢。
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: 加上判断null的, SELECT A.*,(ifnull(CountZhiYu,0)+ifnull(CouuntDeYu,0)+ifnull(CountTiYu,0)) count from( select studentID,studentName ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1%')*0.7 CountZhiYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '2%')*0.2 CouuntDeYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '3%')*0.1 CountTiYu from student a ) a order by count
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: 你一点都不懂呀
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count from( select studentID,studentName ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1%')*0.7 CountZhiYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '2%')*0.2 CouuntDeYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '3%')*0.1 CountTiYu from student a ) a order by count
2 年多之前 回复
my_00
my_00 怎么实现按照Count字段从大到小排序啊?
2 年多之前 回复
my_00
my_00 课程编号有:100001~199999,这个范围内LIKE怎么表示啊?
2 年多之前 回复
my_00
my_00 谢谢你再次相助!
2 年多之前 回复
danielinbiti
danielinbiti 回复my_00: SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count from( select studentID,studentName ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1000%')*0.7 CountZhiYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '2000%')*0.2 CouuntDeYu ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '3000%')*0.1 CountTiYu from student a ) a
2 年多之前 回复
my_00
my_00 你再帮我看看好吗?谢谢你啦!
2 年多之前 回复
my_00
my_00 谢谢你。但是有错误啊?
2 年多之前 回复
u013538542
u013538542   2015.06.07 15:52

可以用inner join , outer join union 等等

select * from Course a union CourseType b union Grade c union Student d

my_00
my_00 但是我没实现。
2 年多之前 回复
my_00
my_00 谢谢
2 年多之前 回复
my_00
my_00   2015.06.07 16:45

谢谢大家的慷慨帮助!
我这样写有红色错误提示:

 SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count
from(
select studentID,studentName 
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '10000%')*0.7 CountZhiYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '20000%')*0.2 CouuntDeYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '30000%')*0.1 CountTiYu
from student
) a

 错误提示:
 消息 4104,级别 16,状态 1,第 5 行
无法绑定由多个部分组成的标识符 "A.studentID"。
消息 4104,级别 16,状态 1,第 6 行
无法绑定由多个部分组成的标识符 "A.studentID"。
消息 4104,级别 16,状态 1,第 7 行
无法绑定由多个部分组成的标识符 "A.studentID"。

我该怎么改啊?

my_00
my_00   2015.06.07 17:53

还有一点需要优化:问什么有的count字段的数据是NULL??但是前面三个不都是NULL啊?例如:第三个:学生何洁,CountZhiYu是56.0,那么他的count应该是56.0才对啊!??
如下图所示:
图片说明

my_00
my_00   2015.06.07 21:58

首先感谢大家的慷慨帮助!再者,我还有一个问题需要完善。

 我这样得出的结果是表student里所有同学的成绩,所以没录入成绩的学生也显示成绩为0。但是我想只得到表Grade里所有已录入成绩的学生的成绩。 
 代码如下:
 SELECT A.*,(isnull(CountZhiYu,0)+isnull(CountDeYu,0)+isnull(CountTiYu,0)) Count 
from( select studentID,studentName ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1%')*0.7 CountZhiYu ,(select sum(grade) 
from Grade where studentID=A.studentID and courseID LIKE '2%')*0.2 CountDeYu ,(select sum(grade) 
from Grade where studentID=A.studentID and courseID LIKE '3%')*0.1 CountTiYu from student a ) a 
order by count DESC

我该如何对代码作改动呢??恳请再次相助!!

datou431
datou431   Rxr 2015.06.09 18:32

可以join,然后group by

Csdn user default icon
上传中...
上传图片
插入图片