sql-server - SQL Server - 将多个查询结果合并为一个结果集

Question

我有三个查询，每个查询返回一列。

SELECT Name FROM Tenant
SELECT Name FROM Space
SELECT ID FROM Contracts

表定义是:

Tenant    (ID, Name)
Space     (ID, Name, TenantID)
Contracts (ID, TenantID)

我在这些表中的信息是:

+----+---------+
| id |  name   |
+----+---------+
|  1 | Tenant1 |
|  2 | Tenant2 |
|  3 | Tenant3 |
+----+---------+

+----+------+----------+
| id | name | tenantID |
+----+------+----------+
|  1 | S1   |        1 |
|  2 | S2   |        1 |
|  3 | S3   |        2 |
|  4 | S4   |        3 |
|  5 | S5   |        3 |
+----+------+----------+

+----+----------+
| id | tenantID |
+----+----------+
|  1 |        1 |
|  2 |        1 |
|  3 |        2 |
|  4 |        2 |
|  5 |        2 |
|  6 |        3 |
+----+----------+

如何编写查询来实现以下结构？

+----------+-------+----------+
|  tenant  | space | contract |
+----------+-------+----------+
| tenant 1 | S1    |        1 |
|          | S2    |        2 |
| tenant 2 | S3    |        3 |
|          |       |        4 |
|          |       |        5 |
| tenant 3 | S4    |        6 |
|          | S5    |          |
+----------+-------+----------+

我在 Tenant 和 Contracts 表之间有链接，但我不希望它们考虑租户之间的空间，我不希望任何列中的值重复。

我试过使用连接，但如果它们之间存在匹配，它们显然会复制列中的值。

SELECT T.NAME 'Tname',
    S.NAME 'Sname',
    C.ID
FROM Tenant T
LEFT JOIN Space S
    ON T.ID = S.TenantID
LEFT JOIN Contracts C
    ON T.ID = C.TenantID

我还尝试将其关联到子查询中并使用 ROW_NUMBER() 并结合一些 CASE 语句来实现所需的格式，但不是很成功.

这是一个SQLFiddle带有一些示例数据。

非常感谢任何可能有帮助的建议/评论或链接。

Answer 1

WITH SpaceRow AS (
    SELECT tenantid
          ,name
          ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Space
)
,ContractRow AS (
    SELECT tenantid
          ,id
          ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Contracts
)
,SpaceContracts AS (
    SELECT COALESCE(SpaceRow.tenantid, ContractRow.tenantid) AS tenantid
          ,COALESCE(SpaceRow.RowNumber, ContractRow.RowNumber) AS RowNumber
          ,SpaceRow.name AS SpaceName
          ,ContractRow.id AS ContractId
    FROM SpaceRow
         FULL OUTER JOIN ContractRow
              ON SpaceRow.tenantid = ContractRow.tenantid
                 AND SpaceRow.RowNumber = ContractRow.RowNumber
)
SELECT CASE WHEN SpaceContracts.RowNumber IS NULL  
                 OR SpaceContracts.RowNumber = 1
            THEN Tenant.name 
            ELSE NULL
        END AS TenantName
       ,SpaceContracts.SpaceName
       ,SpaceContracts.ContractId
FROM Tenant
     LEFT JOIN SpaceContracts
         ON SpaceContracts.tenantid = Tenant.id
ORDER BY Tenant.id
        ,SpaceContracts.RowNumber