数据库选择语句返回的行数多于预期 [英] Database select statement returning more rows than expected

问题描述

大家好，



我正在使用select语句从数据库中检索记录，但因为我在表中存储了value属性，所以我必须连接多个表才能显示文本属性，在加入其他表之前，结果出来很好，返回四行，加入表后我有14行，我试着调试它，我似乎没有看到问题来自哪里。如果有人可以提供协助，我将不胜感激。



我的尝试：



加入表格之前的脚本：

Hello everyone,

I am retrieving records from database using select statement, but because i stored value property in the table i have to join multiple tables in order to display the text property, before joining the other tables, the result came out fine returning four rows, after joining the table i got 14 rows, i tried to debug it, i didnt seem to see where the issue is coming from. I will appreciate if anyone could assist.

What I have tried:

Script before joining tables:

SELECT DISTINCT 
    ,rs.[ResultID]
    ,rs.[StudentNo_]
    ,rs.[House]	
    ,rs.[DOB]
    ,rs.[AcademicSession]
    ,rs.[Term]
    ,rs.[TeacherRemark]
    ,rs.[MarkObtainable]
    ,rs.[MarkObtained]
    ,rs.[Subject]
    ,rs.[School] 	
    ,rs.[CATest1] 
    ,rs.[CATest2]
    ,rs.[CATest3]
    ,rs.[CATest4]
    ,rs.[CATest5]
    ,rs.[ExamScore]
    ,rs.[HeadTeacherRemark]
    ,rs.[CATotal]
    ,rs.[Grade]
    ,rs.[Remark]
    ,rs.[Class]
    ,rs.[ClassAverage]
    FROM [Results] rs    
    WHERE rs.[StudentNo_] = @StudentNumber AND rs.[Term] = @Term AND rs.  [AcademicSession] = @AcademicSession AND rs.[Class] = @Class AND rs.[School] =     @School

加入表格后的
脚本：

script after joining tables:

SELECT DISTINCT pss.[Code] SubCode
      ,ss.[Description] SubjectName
	  ,pss.[Subject Code] SubjectCode
	  ,pss.[Core Subject] CoreSubject
	  ,pss.[CA Calculator] CACalculator
	  ,pss.[Grade Display] GradeDisplay
	  ,pss.[Term Total Calculator] TermTotalCalculator
      ,rs.[ResultID]
      ,rs.[StudentNo_]
	  ,sd.[FullName]
	  ,sd.[ImagePath]
	  ,sd.[Gender]
      ,sd.[House]	  
      ,sd.[Date Of Birth] DOB
      ,ass.[Description] AcademicSession
      ,rs.[Term]
      ,rs.[TeacherRemark]
      ,rs.[MarkObtainable]
      ,rs.[MarkObtained]
      ,rs.[Subject]
      ,sl.[Name] School	  
      ,rs.[CATest1] 	  
      ,rs.[CATest2]
      ,rs.[CATest3]
      ,rs.[CATest4]
      ,rs.[CATest5]
      ,rs.[ExamScore]
      ,rs.[HeadTeacherRemark]
      ,rs.[CATotal]
      ,rs.[Grade]
      ,rs.[Remark]
      ,rs.[Class]
	  ,rs.[ClassAverage]
  FROM [Results] rs
  INNER JOIN [PrimarySubjectSetUp] pss ON rs.[Subject] = pss.[Subject Code]
  INNER JOIN [SchoolList] sl ON rs.[School] = sl.[Code]
  INNER JOIN [Student] sd ON rs.StudentNo_ = sd.No_
  INNER JOIN [School Subjects] ss ON pss.[Subject Code] = ss.[Code]
  INNER JOIN [AcademicSession] ass ON rs.[AcademicSession] = ass.Code
  WHERE rs.[StudentNo_] = @StudentNumber AND rs.[Term] = @Term AND rs.[AcademicSession] = @AcademicSession AND rs.[Class] = @Class AND rs.[School] = @School
  ORDER BY pss.[Subject Code] ASC

推荐答案

As在评论中提到，多个记录的原因是因为您在联接中添加的表中至少有一个表有多个匹配记录。



很高兴听到您的声音搞定了。

As mentioned in comments, the reason for the multiple records is because at least one of the tables you added in your joins has more than one matching record.

Glad to hear you got it worked out.

这篇关于数据库选择语句返回的行数多于预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！