一个查询中的多个查询在哪里(multiple query in one query where)
我写了这个查询来查找2个表中的最高分(选择项: StudentId和Score ),现在我想从其他表中选择一些其他数据:( StudentName , StudentImage ,...),这些表必须使用StudentId过滤项目
我的查询返回:
StudentId HighScoreUser -1 250 -2 100 -3 90 -4 80 -5 40为了在网格中显示数据,我需要学生姓名,...所以我必须使用StudentId来查找特定用户的信息:
CREATE PROCEDURE SelectTopYear AS SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser FROM (SELECT StudentId, Score FROM tbl_ActPoint UNION ALL SELECT StudentId, Score FROM tbl_EvaPoint ) as T GROUP BY StudentId ORDER BY HighScoreUser DESC RETURN 0I wrote this query for finding the highest score from 2 tables (Selected Item: StudentId and Score), and now I want to select some other data: (StudentName, StudentImage, ...) from other tables that must filter items using StudentId
my query returns:
StudentId HighScoreUser -1 250 -2 100 -3 90 -4 80 -5 40For showing data in a grid, I need the Student Name, ... so I must use StudentId to find the info for the specific user:
CREATE PROCEDURE SelectTopYear AS SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser FROM (SELECT StudentId, Score FROM tbl_ActPoint UNION ALL SELECT StudentId, Score FROM tbl_EvaPoint ) as T GROUP BY StudentId ORDER BY HighScoreUser DESC RETURN 0最满意答案
您可以使用CTE(或子查询)和JOIN :
WITH s as ( SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser FROM (SELECT StudentId, Score FROM tbl_ActPoint UNION ALL SELECT StudentId, Score FROM tbl_EvaPoint ) s GROUP BY StudentId ORDER BY HighScoreUser DESC ) SELECT . . . FROM s JOIN othertable ot ON s.StudentId = ot.StudentId;填写适当的列名和表名。
You can use a CTE (or subquery) and JOIN:
WITH s as ( SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser FROM (SELECT StudentId, Score FROM tbl_ActPoint UNION ALL SELECT StudentId, Score FROM tbl_EvaPoint ) s GROUP BY StudentId ORDER BY HighScoreUser DESC ) SELECT . . . FROM s JOIN othertable ot ON s.StudentId = ot.StudentId;Fill in the appropriate column names and table names.
更多推荐
发布评论