具有多列的“Where IN”（SQL Server）

"Where IN" with multiple columns (SQL Server)

提问by Jonathan Leffler

I'm using SQL Server 2005.

我正在使用 SQL Server 2005。

The query would look like this

查询看起来像这样

Select col1, col2, col3 from <subquery> where (col1,col2) in <subquery>

SQL Server doesn't seem to like that. Any way of implementing that that anyone knows of that doesn't involve converting to varchars or anything else messy?

SQL Server 似乎不喜欢那样。任何人都知道的实现方式不涉及转换为 varchars 或其他任何混乱的东西？

This is the actual query.

这是实际的查询。

SELECT * 
FROM         
 (
    SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole
    FROM dbo.Portal_UserRoles

    UNION

    SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
           dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, 
           dbo.Portal_GroupRoles.GroupId AS GroupRole
    FROM dbo.Portal_GroupRoles 
    INNER JOIN dbo.Portal_UserGroups 
        ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
  ) AS derivedtbl_1
WHERE (derivedtbl_1.RoleId,derivedtbl_1.ClubId) IN 
      (
         SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
         FROM Portal_GroupRoles 
         INNER JOIN Portal_ClubGroups
             ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
      )

采纳答案by Eduardo Molteni

You have to separate in two clauses

你必须分开两个子句

 where col1 in (...) AND col2 in (...)

or you could refactor it a little bit

或者你可以稍微重构一下

select * FROM (
     SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole FROM dbo.Portal_UserRoles 
     UNION 
     SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, dbo.Portal_GroupRoles.GroupId AS GroupRoles FROM dbo.Portal_GroupRoles INNER JOIN dbo.Portal_UserGroups ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId) 
     AS derivedtbl_1, Portal_GroupRoles, Portal_ClubGroup
where derivedtbl_1.RoleId = Portal_GroupRoles.RoleId
and derivedtbl_1.ClubId = Portal_ClubGroups.ClubId
and Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId

回答by Jonathan Leffler

The standard, classic way to do what you seek is an EXISTS clause:

执行您所寻求的标准的经典方法是 EXISTS 子句：

SELECT * 
    FROM         
    (
        SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole
        FROM dbo.Portal_UserRoles

        UNION

        SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
               dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, 
               dbo.Portal_GroupRoles.GroupId AS GroupRole
            FROM dbo.Portal_GroupRoles 
                 INNER JOIN dbo.Portal_UserGroups 
                 ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
    ) AS derivedtbl_1
WHERE EXISTS
  (
     SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
         FROM (Portal_GroupRoles 
               INNER JOIN Portal_ClubGroups
               ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId) AS cgr
         WHERE derivedtbl_1.RoleID = cgr.RoleId
           AND derivedtbl_1.ClubId = cgr.ClubId
  )

Be wary of splitting the two-column condition into two separate IN clauses; it does not give you the same answer (in general) as the applying the two-column condition in one EXISTS clause.

小心将两列条件拆分为两个单独的 IN 子句；它不会为您提供与在一个 EXISTS 子句中应用两列条件相同的答案（通常）。

回答by cmsjr

Do a join on the derived table instead of using the in

对派生表进行连接，而不是使用 in

SELECT * 
FROM  
  (
    SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole 
    FROM dbo.Portal_UserRoles 

    UNION 

    SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
           dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId,
           dbo.Portal_GroupRoles.GroupId AS GroupRole 
    FROM dbo.Portal_GroupRoles 
    INNER JOIN dbo.Portal_UserGroups 
        ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
  ) AS derivedtbl_1 

INNER JOIN 
  (
    SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId 
    FROM Portal_GroupRoles 
    INNER JOIN Portal_ClubGroups 
        ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
  ) derivedtbl_2
    ON derivedtbl_1.RoleID = derivedtbl_2.RoleID 
      AND derivedtbl_1.ClubId = derivedtbl_2.ClubId

回答by Michael Buen

SELECT 
    /*      
    your selected fields, joins here
    */
WHERE -- (derivedtbl_1.RoleId,derivedtbl_1.ClubId) IN 
    EXISTS
    (
        -- actually you can change these two fields to * (asterisk ) or 1, whatever, even your name, what matters only is the testing of existence(see below)
        SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId 
        FROM Portal_GroupRoles 
        INNER JOIN Portal_ClubGroups
        ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId

        -- here is your IN (the testing of existence):
        WHERE Portal_GroupRoles.RoleId = derivedtbl_1.RoleId AND 
        AND derivedtbl_1.ClubId = derivedtbl_1.ClubId
      )

alternatively:

SELECT 
    /*      
    your selected fields, joins here
    */
JOIN 
    (
        SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
        FROM Portal_GroupRoles 
        INNER JOIN Portal_ClubGroups
        ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
    ) X 
    -- here is your IN:
    ON X.RoleId = derivedtbl_1.RoleId
    AND X.ClubId = derivedtbl_1.ClubId

回答by onedaywhen

Yours is useful syntax and is supported in standard SQL. It is not yet implemented in SQL Server but you may vote for its inclusion here.

你的语法很有用，标准 SQL 支持。它尚未在 SQL Server 中实现，但您可以在此处投票支持将其包含在内。