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