子查询内的 SELECT INTO

Question

环境：SQL Server 2005

我有这种类型的请求

SELECT *
FROM user
WHERE EXISTS(
  SELECT *
  FROM user_access
  WHERE user_access.user_id = user.user_id
  AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
)
AND EXISTS(
SELECT *
FROM user_service
WHERE user_service.role ='Manager'
AND user.id_user = user_service.user_id
)
AND user.is_active ='True'

但在结果中我还想从子查询中获取结果，所以我考虑了类似的事情

DECLARE @user_access TABLE(user_id int,access nvarchar(30))
DECLARE @user_service TABLE(user_id int,service_id int,role nvarchar(10))
SELECT *  FROM user
    WHERE EXISTS(
      SELECT user_id,access INTO  [@user_access]
      FROM user_access
      WHERE user_access.user_id = user.user_id
      AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
    )
AND EXISTS(
SELECT user_id , service_id,role INTO @user_service
FROM user_service
WHERE user_service.role ='Manager'
AND user.id_user = user_service.user_id
)
    AND user.is_active ='True'


SELECT * FROM @user_acess
select * from @user_service

但我收到以下错误：

“关键字“INTO”附近的语法不正确。”

你知道我如何在不执行两次子查询的情况下做到这一点吗（我尝试使用临时表，同样的错误）？

编辑：我对试图解决我的问题的同事感到抱歉，我忘记了一件事：我有 2 个子查询。查看请求。我想要：

• 所有具有高或中访问权限且担任管理员的活跃用户
• 这些用户所在的所有服务
• 这些用户的所有访问权限

Answer 1

临时表或变量似乎是最简单的解决方案。将子查询的结果填充到临时表或变量中，然后在过程末尾执行两个 select 语句。

Declare @UserAccesses Table (
                            user_id ...
                            , access varchar(...)
                            )

Insert @UserAccesses( user_id, access )
Select UA.user_id, UA.access
From user_access As UA
    Join user As U
        On U.user_id = UA.user_Id
Where U.is_active = 'True'
    And UA.access In('HIGH_LEVEL', 'MEDIUM_LEVEL')

Select ...
From user
Where Exists    (
                Select 1
                From @UserAccess As UA1
                Where UA1.user_id = user.user_id
                )

Select user_id, access
From @UserAccesses

根据原始问题的扩展进行更新

两个子查询的解决方案本质上与一个子查询的解决方案相同，只是您返回三个而不是两个结果集。和以前一样，每个子查询使用临时表/变量：

Declare @UserAccesses Table (
                            user_id int
                            , access nvarchar(30)
                            )

Declare @UserServices Table (
                            user_id int
                            , service_id int
                            , role nvarchar(10)
                            )

Insert @UserAccesses( user_id, access )
Select UA.user_id, UA.access
From user_access As UA
    Join user As U
        On U.user_id = UA.user_Id
Where U.is_active = 'True'
    And UA.access In('HIGH_LEVEL', 'MEDIUM_LEVEL')

Insert @UserServices( user_id, service_id, role )
Select user_id , service_id,role
From user_service
Where user_service.role ='Manager'
    And Exists  (
                Select 1
                From @UserAccesses As UA1
                Where UA1.user_id = user_service.user_id
                )

Select ...
From user
Where Exists    (
                Select 1
                From @UserServices As US1
                Where US1.user_id = user.user_id
                )

Select user_id, access
From @UserAccesses As UA
Where Exists    (
                    Select 1
                    From @UserServices As US1
                    Where US1.user_id = UA.user_id
                    )

Select user_id, access
From @UserServices

Answer 2

您可以拉出子查询来创建临时表，然后从临时表中执行您想要的两个查询。

Answer 3

这是您可以做的最好的优化。无法捕获和保留 EXISTS 子查询中的数据 - 尤其当 EXISTS 未完全评估结果集时。当在子查询中找到单个匹配项（用户的一个访问）时，它会短路，因此您无论如何都无法从中获取所有访问记录。

declare @user table (user_id int)
insert @user
SELECT [user].user_id
FROM [user]
WHERE EXISTS(
    SELECT *
    FROM user_access
    WHERE user_access.user_id = [user].user_id
    AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
    )
AND EXISTS(
    SELECT *
    FROM user_service
    WHERE user_service.role ='Manager'
    AND [user].id_user = user_service.user_id
    )
AND [user].is_active ='True'

SELECT [user].* FROM [user] inner join @user u on u.USER_ID = [user].user_id
SELECT a.user_id, a.access FROM user_access a inner join @user u on u.USER_ID = a.user_id
SELECT s.user_id, s.service_id, s.role FROM user_service s inner join @user u on u.USER_ID = s.user_id

Answer 4

这是 Ken Down^ 建议的实现，我认为它考虑到您的需求，结果不应包含任何无关的行。

DECLARE @user_access TABLE(user_id int,access nvarchar(30))
DECLARE @user_service TABLE(user_id int,service_id int,role nvarchar(10))

INSERT INTO @user_access
SELECT ua.user_id, ua.access
FROM user_access ua
  INNER JOIN [user] u ON ua.user_id = u.user_id
WHERE u.is_active ='True'
  AND ua.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')

INSERT INTO @user_service
SELECT us.user_id , us.service_id, us.role
FROM user_service us
  INNER JOIN [user] u ON us.user_id = u.user_id
WHERE u.is_active ='True'
  AND us.role ='Manager'


SELECT u.*
FROM [user] u
  INNER JOIN (
    SELECT user_id FROM @user_access
    UNION
    SELECT user_id FROM @user_service
  ) uas ON uas.user_id = u.user_id
SELECT * FROM @user_acess
SELECT * FROM @user_service

Answer 5

这应该返回两个表的结果集。

SELECT *
FROM user u, user_access a
WHERE u.user_id in (
  SELECT user_access.user_id
  FROM user_access
  WHERE user_access.user_id = u.user_id
  AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
  AND user_access.user_id = u.user_id
)
AND a.user_id = u.user_id
AND u.is_active ='True'