首页 新闻 搜索 专区 学院

sql高手进比较高级的查询语句

0
悬赏园豆:10 [已解决问题] 解决于 2013-07-31 09:23

表结构如图

1.现在要查询出每个学生所报的课程,并用逗号连接起来,格式如下:班级,学生名,课程。比如20120001学生,查出来如下:化学一班,张三,'数学,语文,英语'

2.查询出每个科目最后一次考试(比如2012年12月的考试)每个科目的前三名

3.查询出与学生20120001所报课程至少两门一样,并且成绩也一样的所有记录

高手进!谢谢了,数据库下载:http://dl.vmall.com/c0cdz2luzr

问题补充:

再查出英语成绩大于语文成绩的同学

code先生-null的主页 code先生-null | 菜鸟二级 | 园豆:307
提问于:2012-12-29 17:21
< >
分享
最佳答案
0

第一问

WITH sc AS (

SELECT s.StudentNo
     , stuff((SELECT ',' + c.CourseName
              FROM
                  Course c
                  JOIN Score s2
                      ON c.CourseNo = s2.CourseNo
              WHERE
                  s2.StudentNo = s.StudentNo
              FOR XML
                  PATH ('')), 1, 1, '') AS Courses
FROM
    Score s
GROUP BY
    s.StudentNo
)

SELECT c.ClassName
     , s.StudentName
     , sc.Courses
FROM
    Student s
    JOIN Class c
        ON s.ClassNo = c.ClassNo
    LEFT JOIN sc
        ON sc.StudentNo = s.StudentNo

第二问  没计算成绩并列的

WITH Rnk AS
(
SELECT StudentNo, Score2Time, CourseNo, Score2, row_number() OVER (PARTITION BY CourseNo ORDER BY Score2 DESC) as Rnk
FROM Score
)

SELECT Course.CourseName, rnk.Score2Time, rnk.Score2, Student.*, Rnk.Rnk
FROM Rnk JOIN Student on rnk.StudentNo = Student.StudentNo
JOIN Course on rnk.CourseNo = Course.CourseNo
WHERE Rnk.Rnk < 4

第三问

/****** Script for SelectTopNRows command from SSMS  ******/

WITH fellowStudents as
(
SELECT *
     , (SELECT count(*)
        FROM
            (SELECT [CourseNo]
                  , [Score2]
             FROM
                 [StudentInfo].[dbo].[Score]
             WHERE
                 StudentNo = 'S00001'
             INTERSECT
             SELECT CourseNo
                  , [Score2]
             FROM
                 [StudentInfo].[dbo].[Score]
             WHERE
                 Score.StudentNo = s.StudentNo) base) AS SameCourseNo

FROM
    Student s
)

SELECT *
FROM
    fellowStudents
WHERE
    SameCourseNo >= 2
收获园豆:10
gunsmoke | 老鸟四级 |园豆:3592 | 2012-12-31 08:41

朋友,可以加我Q吗?1059533421,里面有些不是太理解

code先生-null | 园豆:307 (菜鸟二级) | 2013-01-11 12:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册