SQL - 带有条件的自连接"n"次

Question

我有下表：

Id                                    | Type
--------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  |  1
C7B1752D-FD30-445A-AD6C-51D1434607D3  |  2
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  |  3
--------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912  |  1
546519ED-5E78-4DAD-ADFF-9DC0AA67B763  |  2
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4  |  3
--------------------------------------------
-- ... and so on ... --

现在，我需要一个特定类型的 SELECT(像这样(：

SELECT
    [Id] AS [OneId]   -- Where [Type] = 1,
    [Id] AS [TwoId]   -- Where [Type] = 2,
    [Id] AS [ThreeId] -- Where [Type] = 3
FROM Table

这是我到目前为止尝试过的，但结果很糟糕：

SELECT
    oneI.[Id] AS [OneId]   -- Where [Type] = 1,
    twoI.[Id] AS [TwoId]   -- Where [Type] = 2,
    threeI.[Id] AS [ThreeId] -- Where [Type] = 3
FROM Table AS i
INNER JOIN Table AS oneI ON
    i.[Id] = oneI.[Id]
INNER JOIN Table AS twoI ON
    i.[Id] = twoI.[Id]
INNER JOIN Table AS threeI ON
    i.[Id] = threeI.[Id]
WHERE 
    oneI.[Type] = 1
AND twoI.[Type] = 2
AND threeI.[Type] = 3

甚至更糟(这给了我更多很多组合(：

SELECT
    oneI.[Id] AS [OneId]   -- Where [Type] = 1,
    twoI.[Id] AS [TwoId]   -- Where [Type] = 2,
    threeI.[Id] AS [ThreeId] -- Where [Type] = 3
FROM Table AS i, TABLE AS oneI, Table AS twoI, Table AS threeI
WHERE 
    oneI.[Type] = 1
AND twoI.[Type] = 2
AND threeI.[Type] = 3

编辑：

我还会有另一列，我将对这些 ID 进行分组。因此，更新后的表如下所示：

Id                                               | GroupId | Type
------------------------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  -- OneId   |    1    |  1 
C7B1752D-FD30-445A-AD6C-51D1434607D3  -- TwoId   |    1    |  2 
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  -- ThreeId |    1    |  3 
------------------------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912 -- OneId    |    2    |  1 
546519ED-5E78-4DAD-ADFF-9DC0AA67B763 -- TwoId    |    2    |  2 
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4 -- ThreeId  |    2    |  3 
----------------------------------------------------------------

因此，我需要以下结果：

OneI   | TwoI  | ThreeI   | GroupId
------------------------------------------------------
OneId  | TwoId | ThreeId  |    1
OneId  | TwoId | ThreeId  |    2

编辑：

我还有一个特殊情况 - [Type]列可以重复：

Id                                                    | GroupId | Type
-----------------------------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  -- OneId        |    1    |  1 
C7B1752D-FD30-445A-AD6C-51D1434607D3  -- TwoId        |    1    |  2 
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  -- ThreeId      |    1    |  3 
FEB4A345-FEA0-4530-AE52-6CF4F07E37BA  -- OtherThreeId |    1    |  3 
-----------------------------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912 -- OneId         |    2    |  1 
546519ED-5E78-4DAD-ADFF-9DC0AA67B763 -- TwoId         |    2    |  2 
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4 -- ThreeId       |    2    |  3 
----------------------------------------------------------------

现在的结果将是：

OneI   | TwoI  | ThreeI        | GroupId
------------------------------------------------------
OneId  | TwoId | ThreeId       |    1
OneId  | TwoId | OtherThreeId  |    1
OneId  | TwoId | ThreeId       |    2

SQLfiddle

Answer 1

嗯，这需要一些顺序排序列，但你也可以将其表示为

select max(case when [Type] = 1 then Id end) OneId,
       max(case when [Type] = 2 then Id end) TwoId,
       max(case when [Type] = 3 then Id end) ThreeId
from (select *, 
             row_number() over (order by (select 1)) Seq 
      from table
     ) t
group by (Seq - [Type]);

编辑 ：- 但是，如果您还想包含group则将它们用作 select 语句

select (Seq - [Type]) as GroupId,
       max(case when [Type] = 1 then 'OneId' end) OneI,
       max(case when [Type] = 2 then 'TwoId' end) TwoI,
       max(case when [Type] = 3 then 'ThreeId' end) ThreeI
from (select *, 
             row_number() over (order by (select 1)) Seq 
      from table
      ) t
group by (Seq - [Type]);

对于更新后的表，您可以直接在GroupId列中使用带有group by子句的表，因为这样您就不使用row_number()函数和子查询

select max(case when [Type] = 1 then 'OneId' end) OneI,
       max(case when [Type] = 2 then 'TwoId' end) TwoI,
       max(case when [Type] = 3 then 'ThreeId' end) ThreeI,
       GroupId
from table t
group by GroupId;

演示

Answer 2

您需要

一个Grp属性，然后指定哪些行在一个组中。像这样的东西

Id                                    | Type |  Grp
----------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  |  1   |   1
C7B1752D-FD30-445A-AD6C-51D1434607D3  |  2   |   1
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  |  3   |   1
---------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912  |  1   |   2
546519ED-5E78-4DAD-ADFF-9DC0AA67B763  |  2   |   2
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4  |  3   |   2
--------------------------------------------------

然后你可以像这样使用条件聚合

SELECT
     MAX(CASE WHEN [Type] = 1 THEN [Id] END) AS [OneId],
     MAX(CASE WHEN [Type] = 2 THEN [Id] END) AS [TwoId],
     MAX(CASE WHEN [Type] = 3 THEN [Id] END) AS [ThreeId]
FROM YourTable
GROUP BY Grp

Answer 3

看起来这可以为您完成工作

; WITH CTE
AS
(
SELECT
    Id,
    [Type]
    FROM YourTable
       WHERE [Type]
       IN
       (
          1,2,3,4,5
       )
)
SELECT
    *
    FROM CTE
    PIVOT
    (
       MAX(id)
       FOR [Type] IN
       (
          [1],[2],[3],[4],[5]
       )
    )Pvt