在一条SQL语句中加入多个选择语句

I am trying to join two sql statements into one but have had no success.

我正在尝试将两个sql语句合并到一个语句中，但是没有成功。

I would ideally like the Average query to be added to the end of the columns from the first sql statement.

理想情况下，我希望将查询添加到第一个sql语句的列的末尾。

First statement:

首先声明:

SELECT Modules.UserID, Module_Info.ModuleTitle, Modules.ModuleMarks, Modules.ExamMark, Modules.AssignmentMark, MarkClassification.MarkDescription
FROM Module_Info 
INNER JOIN Modules ON Module_Info.ModuleID = Modules.ModuleID 
INNER JOIN MarkClassification ON Modules.MarkCodeDescription = MarkClassification.MarkId
WHERE(Modules.UserID = '8')

Second statement:

第二句:

SELECT AVG(Modules.ExamMark) AS Average    
FROM Module_Info     
INNER JOIN Modules ON Module_Info.ModuleID = Modules.ModuleID
WHERE (Modules.ModuleID = '2')

2 个解决方案

#1

You can use a sub query in the select for that, like this:

您可以在select中使用子查询，如下所示:

SELECT Modules.UserID, Module_Info.ModuleTitle, Modules.ModuleMarks,
       Modules.ExamMark, Modules.AssignmentMark, MarkClassification.MarkDescription,
       (SELECT AVG(Modules.ExamMark) 
        FROM Module_Info 
        INNER JOIN Modules ON Module_Info.ModuleID = Modules.ModuleID
        WHERE (Modules.ModuleID = '2')) as Average
FROM Module_Info 
INNER JOIN Modules
 ON Module_Info.ModuleID = Modules.ModuleID 
INNER JOIN MarkClassification
 ON Modules.MarkCodeDescription = MarkClassification.MarkId
WHERE(Modules.UserID = '8')

#2

you can try

你可以试着

SELECT * FROM

(SELECT Modules.UserID, Module_Info.ModuleTitle, Modules.ModuleMarks, Modules.ExamMark, Modules.AssignmentMark, MarkClassification.MarkDescription
FROM Module_Info
INNER JOIN Modules ON Module_Info.ModuleID = Modules.ModuleID
INNER JOIN MarkClassification ON Modules.MarkCodeDescription = MarkClassification.MarkId
WHERE(Modules.UserID = '8')) t1

JOIN

(SELECT AVG(Modules.ExamMark) AS Average
FROM Module_Info
INNER JOIN Modules ON Module_Info.ModuleID = Modules.ModuleID
WHERE (Modules.ModuleID = '2')) t2

Since you do not have a join condition, it will create a cartesian product of the two queries (i.e. each row of the first subselect, with each row of the second subselect). Since the 2nd subselect only has one row, it will give you your expected result.

由于没有连接条件，它将创建两个查询的笛卡尔积(即第一个子选择的每一行，以及第二个子选择的每一行)。由于第2次选择只有一行，它将给出您的预期结果。

#1