一个查询中的多个查询在哪里(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 0

I 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 40

For 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.

更多推荐