如何合并两个SQL select语句?

时间:2022-06-25 00:22:33

I wrote a query in SQL with two select statement, but I get an error as the result.

我在SQL中用两个select语句编写了一个查询,但结果却出错了。

My query is :

我的查询是:

 SELECT
     ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNum',
     Employment_Tests_Courses.Title as [Course],
     Employment_Tests_Courses.ID,
     Employment_Tests_Courses.TID,
     (SELECT T1.Title as [Title Exam] 
      FROM Employment_Tests_Title T1  
      INNER JOIN Employment_Tests_Courses T2 ON T1.ID = T2.TID),
     (SELECT COUNT(*) 
      FROM Employment_Tests_Questions 
      WHERE Employment_Tests_Courses.ID = Employment_Tests_Questions.CID) as [Question Count]
 FROM
     Employment_Tests_Courses

but in the output I get this error :

但在输出中我收到此错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

子查询返回的值超过1。当子查询遵循=,!=,<,<=,>,> =或子查询用作表达式时,不允许这样做。

In middle select's statement i get back 42 rows and all rows count is same.

在中间选择的语句中,我得到42行,所有行数都相同。

The tables view is:

表格视图是:

Employment_Tests_Title

ID     Title

Employment_Tests_Courses

ID     TID (Employment_Tests_Title ID)     Title

Employment_Tests_Questions

ID      TID (Employment_Tests_Title ID)     Title      CID (Employment_Tests_Course ID)

Please help me how can I write this query.

请帮我,我该怎么写这个查询。

Thanks

2 个解决方案

#1


1  

Actually, the error is in this statement:

实际上,错误在于此声明:

(SELECT T1.Title as [Title Exam] 
  FROM Employment_Tests_Title T1  
  INNER JOIN Employment_Tests_Courses T2 ON T1.ID = T2.TID),

The reason is that this returns more than 1 result according to your error (without data it's hard to check, but i'm pretty sure this is the case).

原因是这会根据您的错误返回超过1个结果(没有数据很难检查,但我很确定是这种情况)。

#2


0  

Select 
    ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNum',
    Courses.Title as [Course],
    Courses.ID, 
    Courses.TID,
    T1.Title as [Title Exam],
    (select count(*)
         From Employment_Tests_Questions Q
         where Courses.ID = Q.CID) 
    as [Question Count]
FROM Employment_Tests_Courses Courses
JOIN Employment_Tests_Title T1
ON T1.ID=Courses.TID

#1


1  

Actually, the error is in this statement:

实际上,错误在于此声明:

(SELECT T1.Title as [Title Exam] 
  FROM Employment_Tests_Title T1  
  INNER JOIN Employment_Tests_Courses T2 ON T1.ID = T2.TID),

The reason is that this returns more than 1 result according to your error (without data it's hard to check, but i'm pretty sure this is the case).

原因是这会根据您的错误返回超过1个结果(没有数据很难检查,但我很确定是这种情况)。

#2


0  

Select 
    ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNum',
    Courses.Title as [Course],
    Courses.ID, 
    Courses.TID,
    T1.Title as [Title Exam],
    (select count(*)
         From Employment_Tests_Questions Q
         where Courses.ID = Q.CID) 
    as [Question Count]
FROM Employment_Tests_Courses Courses
JOIN Employment_Tests_Title T1
ON T1.ID=Courses.TID