在SQL中，将null值替换为另一个值

Here is my SQL Query

这是我的SQL查询

SELECT p.StudentID, ai.RollNo, p.FirstName, p.MiddleName, p.LastName, 
om.ExamID,  et.ExamName, om.SubjectID,
ISNULL(CONVERT(varchar(20),om.ObtainedMarksTheory), 'A') as 'ObtainedMarksTheory', 
ISNULL(CONVERT(varchar(20),om.ObtainedPracticalMarks),'A') as 'ObtainedPracticalMarks'
FROM Students.PersonalInfo p
INNER JOIN Students.AcademicCourse ac on p.StudentID = ac.StudentID
INNER JOIN Students.AcademicInfo ai on p.StudentID=ai.StudentID
LEFT OUTER JOIN Exam.ObtainedMarkEntry om on p.StudentID = om.StudentID
LEFT JOIN Exam.ExamType et   on om.ExamID = et.ExamID
WHERE ai.BatchID = '103' AND ai.SemesterID = '21' and ac.Section = '8'

This produce result as in a picture:

这产生的结果如图所示:

But I want result like this since those two students were absent in that exam

但我想要这样的结果,因为那两个学生在考试中缺席

Similarly if another Exam does exists for any of three student and other are absent same procedure should repeat

同样,如果三个学生中任何一个学生存在另一个考试而其他考试不存在,则应重复相同的程序

2 个解决方案

#1

Use IsNULL() Function see below example

使用IsNULL()函数见下面的例子

Declare @variable varchar(MAX)
set @variable = NULL
select IsNULL(@variable,0) as A

#2

Let's have the following sample data (it is like your sample data, just inserted in one table):

让我们有以下示例数据(就像您的示例数据,只是插入一个表中):

DECLARE @DataSource TABLE
(
    [StudentID] TINYINT
   ,[RowNo] TINYINT
   ,[FirstName] VARCHAR(12)
   ,[MiddleName] VARCHAR(12)
   ,[LastName] VARCHAR(12)
   ,[ExamID] TINYINT
   ,[ExamName] VARCHAR(18)
   ,[SubjectID] TINYINT
   ,[ObtainedMarksTheory] VARCHAR(12)
   ,[ObtainedPracticalMarks] VARCHAR(12)
);

INSERT INTO @DataSource ([StudentID], [RowNo], [FirstName], [MiddleName], [LastName], [ExamID], [ExamName], [SubjectID], [ObtainedMarksTheory], [ObtainedPracticalMarks])
VALUES (101, 1, 'FN_A', 'MN_A', 'LN_A', NULL, NULL, NULL, 'A', 'A')
      ,(102, 2, 'FN_B', 'MN_B', 'LN_B', 28, 'First Tem2072', 97, '74.00', '56.00')
      ,(103, 3, 'FN_C', 'MN_C', 'LN_C', NULL, NULL, NULL, 'A', 'A');

SELECT *
FROM @DataSource;

So, I am supposing we have details only for one exam here and the question is how to replace the NULL values of ExamID, ExamName and SubjectID columns using the existing values.

所以,我假设我们这里只有一个考试的详细信息,问题是如何使用现有值替换ExamID,ExamName和SubjectID列的NULL值。

The solution is to use the MAX function with OVER clause:

解决方案是使用MAX函数和OVER子句:

SELECT [StudentID]
      ,[RowNo]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,MAX([ExamID]) OVER() AS [ExamID]
      ,MAX([ExamName]) OVER() AS [ExamName]
      ,MAX([SubjectID]) OVER() AS [SubjectID]
      ,[ObtainedMarksTheory]
      ,[ObtainedPracticalMarks]
FROM @DataSource;

Note, that it is better to use the OVER clause as you are not going to add GROUP BY clauses in your initial query. Also, if you have details for more exams you can use the PARTITION BY clause (if you have some column(s) to distinguish the NULL rows for each exam).

请注意,最好使用OVER子句,因为您不会在初始查询中添加GROUP BY子句。此外,如果您有更多考试的详细信息,则可以使用PARTITION BY子句(如果您有一些列来区分每个考试的NULL行)。