Select Score as CurrentScore, Class, Level as CurrentLevel,
(select Score from tblLevelDefine d where d.Level= tblStudent.CurrentLevel+1 ) as NextScore
From tblStudent
(select Score from tblLevelDefine d where d.Level= tblStudent.CurrentLevel+1 ) as NextScore
From tblStudent
(select Score from tblLevelDefine d where d.Level= a.Level+1 ) as NextScore
From tblStudent a
(select Score from tblLevelDefine d where d.Level= a.CurrentLevel+1 ) as NextScore
From tblStudent a
from
(Select Score as CurrentScore, Class, Level as CurrentLevel From tblStudent) a
(select Score from tblLevelDefine d where d.Level= tblStudent.CurrentLevel+1 ) as NextScore
From tblStudent2、你改为:Select tblStudent.Score as CurrentScore, tblStudent.Class, tblStudent.Level as CurrentLevel,
tblLevelDefine.Score NextScore
From tblStudent left join tblLevelDefine on tblStudent.Level=tblStudent.CurrentLevel+1
但是在复杂一点的Sql语句好像就不work了。比如:
Select max(a.[Score]) as CurrentScore, max(a.Class) as Class, max(a.[Level]) as CurrentLevel, max(b.CourseName) as CourseName
,(select [Score] from tblLevelDefine d where d.[Level]= tblStudent.CurrentLevel + 1 ) as NextScore
From tblStudent a, tblCourse b
Where a.ProfileIdentity = 1 and b.courseID = a.courseID
Group By a.courseID报错同上
首先将Group By出来的东西放进一个临时表,这个临时表也不会大。
然后操作此临时表。DROP Table #temp
Create table #temp (ID Int, CurrentScore float, Class int, Level int, CourseName nvarchar(100))insert into #temp (ID, CurrentScore, Class, Level, CourseName)
Select max(a.[Score]) as CurrentScore, max(a.Class) as Class, max(a.[Level]) as CurrentLevel, max(b.CourseName) as CourseName
From tblStudent a, tblCourse b
Where a.ProfileIdentity = 1 and b.courseID = a.courseID
Group By a.courseIDSelect t.CurrentScore, t.Class, t.[Level], t.CourseName
, (d.Score - CurrentScore) as NextScore
From #temp t, tblLevelDefine d
Where d.Level = t.Level + 1