错误|如果没有使用EXISTS引入子查询，则只能在选择列表中指定一个表达式

Question

我有以下SQL查询，它搜索用户的可用性，以确保他们在两个选定日期之间不可用：

SELECT users.username,
       users.user_id,
       users.first_name,
       users.last_name,
       users.description,
       projects_vs_users.project_id,
       sprints.sprint_start_date,
       sprints.sprint_end_date
FROM   sprints
       INNER JOIN projects
         ON sprints.project_id = projects.project_id
       INNER JOIN projects_vs_users
         ON projects.project_id = projects_vs_users.project_id
       RIGHT OUTER JOIN users
         ON projects_vs_users.user_id = users.user_id
WHERE  ( users.user_id NOT IN (SELECT users_1.username,
                                      users_1.user_id,
                                      users_1.first_name,
                                      users_1.last_name,
                                      users_1.description,
                                      projects_vs_users_1.project_id,
                                      sprints_1.sprint_start_date,
                                      sprints_1.sprint_end_date
                               FROM   sprints AS sprints_1
                                      INNER JOIN projects AS projects_1
                                        ON sprints_1.project_id = projects_1.project_id
                                      INNER JOIN projects_vs_users AS projects_vs_users_1
                                        ON projects_1.project_id = projects_vs_users_1.project_id
                                      RIGHT OUTER JOIN users AS users_1
                                        ON projects_vs_users_1.user_id = users_1.user_id
                               WHERE  ( sprints_1.sprint_start_date BETWEEN CONVERT(DATETIME, @startdate, 103) AND CONVERT(DATETIME, @enddate, 103) )
                                      AND ( sprints_1.sprint_end_date BETWEEN CONVERT(DATETIME, @startdate, 103) AND CONVERT(DATETIME, @enddate, 103) )) ) 

当我单独运行子查询时，它会返回我所需要的值。因此，我认为主查询存在一些问题？

Answer 1

您的子查询

SELECT  users_1.username, users_1.user_id, users_1.first_name, ...

返回8个列-IN条件左侧的表达式只有一个列（user_id）。

错误消息告诉您不能将一列与8列进行比较。您需要将子选择更改为

SELECT users_1.user_id
FROM ...