SQL学生标记查询 [英] SQL students mark query

问题描述

如何选择未在某些子课程中标记的学生的分数

How can I select marks of a student who not marked in some sub lessons

edu_submark:
Id      sm_mark sub_lesson_id    st_id  course_id
1       97       1               9      3
2       66       2               9      3
3       22       2               1012   3
4       32       1               1012   3
1002    15       1               13     3

edu_sub_lesson:
Id      sl_title            lesson_id
1       Active Directory        2
2       Win7                    2
2009    UI                      1
2011    Win SERVER 2008 R2      2

我使用了这个查询：

I used this query:

SELECT 
    a.sl_title, b.sm_mark 
FROM  
    edu_sublesson a 
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id 
WHERE
    lesson_id = 2 AND course_id = 3 AND st_id = 9

结果：

result:

Active Directory 99.9
Win              75

但我想要结果：

But I want this result:

Active Directory 99.9
Win Server       NULL
Win              75
Administration   NULL

我想在gridView中显示标记字段，以便教师可以查看或编辑学生标记，

我们有课程表有tkl课程，

sub_lesson tbl有一节课，

sub_marks有3 fk学生，sub_lesson，课程表

I want to show marks field in a gridView so teacher can see or edit student marks,
we have course table that have a fk of lesson tbl,
sub_lesson tbl have a fk of lesson,
sub_marks have 3 fk of student,sub_lesson ,course tables

推荐答案

你还没有使用表别名在您的查询中，所以很难说哪个列来自哪个表。但无论如何，b的重新分区列应该被允许 NULL 。



例如，如果 st_id 来自 edu_submark ，然后按如下方式修改查询：

You haven't used table aliases in your query so it's quite hard to say which column originates frmo which table. But anyhow the resttricting columns from b should be allowed to be NULL.

For example if st_id comes from edu_submark, then modify the query as follows:

SELECT 
    a.sl_title, b.sm_mark 
FROM  
    edu_sublesson a 
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id 
WHERE lesson_id = 2 
AND course_id = 3 
AND ( st_id = 9 OR st_id IS NULL)

而不是clau se添加左连接中的所有条件

Instead of where clause add all conditions in Left Join

SELECT
    a.sl_title, b.sm_mark
FROM
    edu_sublesson a
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id
    AND lesson_id = 2 
    AND course_id = 3 
    AND st_id = 9

--Declare Teamp table for sub mark

DECLARE @edu_submark AS TABLE (Id  INT IDENTITY(1,1),sm_mark INT,sub_lesson_id  INT,st_id INT ,course_id INT)

-- insert data into sub mark

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 97,1,9,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 66,2,9,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES (22,2,1012,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 32,1,1012,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES (15,1,13,3)

--Declare Teamp table for sub lesson

DECLARE @edu_sub_lesson AS TABLE (Id  INT IDENTITY(1,1), sl_title  NVARCHAR(30), lesson_id INT)


-- insert data into sub lesson

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Active Directory', 2)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Win7', 2)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('UI', 1)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Win SERVER 2008 R2 ', 2)


--Get Data

SELECT
    a.sl_title, ISNULL(b.sm_mark,0)  AS sm_mark
FROM
  @edu_sub_lesson  a
LEFT OUTER JOIN
 @edu_submark b     on a.Id = b.sub_lesson_id

这篇关于SQL学生标记查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！