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    |          |
+----------+-------+----------+

我在租户和合同表之间建立了链接，但我不希望它们考虑租户之间的空格，并且我不希望任何列中的值重复。

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

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。

非常感谢任何建议/评论或有用的链接。

- Radu Gheorghiu

---

您使用结果做什么？格式化和删除重复文本通常在客户端应用程序或报表中执行。 - adrianm

@adrianm 我知道并且同意你的观点，但不幸的是我无法对应用程序进行修改（我没有权限），只能在 SQL 中进行。我使用查询来输出报告的一部分。 - Radu Gheorghiu

如何将“合同”和“空间”关联起来？租户1有两个合同和两个空间，但您如何知道哪个合同适用于哪个空间？ - Raxr

@Bjorn 这就是我的想法，即没有链接。它只是列举了一个“租户”拥有哪些“空间”和“合同”。它们之间没有链接，只有在到达新的租户时才会有一个“换行符”。这就是为什么在我的示例查询中，我不会在“Contracts”和“Space”之间进行JOIN，而是直接与“Tenant”进行JOIN的原因。 - Radu Gheorghiu

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

- adrianm

---

太好了！非常感谢你！ - Radu Gheorghiu

很抱歉，我刚刚观察到这实际上会重复每个租户的“空间”。我会尝试修改您的代码以达到所需的输出。 - Radu Gheorghiu

你能举一个重复的例子吗？SqlFiddle显示了你想要的输出。 - adrianm

你是正确的，根据我提供的数据，你的解决方案是正确的。已将你的答案重新标记为正确。我会继续调整其余部分。 - Radu Gheorghiu

Answer 2

这可能是您问题的一个解决方案：

; WITH spaces AS (
SELECT t.ID AS tID, t.NAME AS tName, s.NAME AS sName, ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY s.ID) AS sSeq
FROM tenant t
LEFT JOIN space s ON t.ID = s.TenantID ),
contrs AS (
SELECT t.ID AS tID, t.NAME AS tName, c.ID AS cID, ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY c.Id) AS tSeq
FROM tenant t
LEFT JOIN contracts c ON t.ID = c.TenantID ),
partial AS (
SELECT s.tName AS tName, s.sName AS sName, c.cID As cID
FROM spaces s
LEFT JOIN contrs c ON s.tID = c.tID AND s.sSeq = c.tSeq )
SELECT (SELECT NAME FROM tenant WHERE tenant.ID = c.TenantID) AS tName, NULL AS sName, c.ID AS cID
FROM contracts c
WHERE c.ID NOT IN (SELECT cID FROM partial WHERE cID IS NOT NULL)

UNION ALL 

SELECT *
FROM partial

ORDER By tName

上述基于CTE的查询会生成以下输出：

tName   sName   cID
--------------------
Tenant1 S1      1
Tenant1 S2      2
Tenant2 S3      3  
Tenant2 NULL    4
Tenant2 NULL    5
Tenant3 S4      6
Tenant3 S5      NULL

这已经足够接近所期望的输出了。

- Giorgos Betsos

Answer 3

你确定这是你想要做的吗？展示所有结果，包括你想要的那些空间中应该有的数据，会更具信息量。如果仅仅因为上面的字段已经显示了该值而删除字段，那么你将限制人们在后续过程中可能想要进行的任何过滤。

此外，根据你提供的数据，你的结果是错误的，例如租户1应该有4条记录而不是2条。他们有2个空间和2个合同，空间和合同之间没有直接连接，这意味着租户1应该有2x2=4条记录。总的来说，你的输出应该有9条记录，而不是7条。在当前结构下，你无法改变这一点。

如果经理要求以这种格式呈现报告中的数据，我建议向他们解释，在这种格式下，它的用处较小，如果包含所有内容，他们可以做更多的事情。例如，如果他们想在Excel中查看它，他们如何过滤出租户1的所有内容？在请求的格式中，如果你对租户1进行过滤，你只会看到一条记录。或者，如果这应该在csv中使用，将加载不正确的数据。除了美学上的优势，你想要的布局没有其他优点，反而削弱了功能性。

- Riller '-'

---

我已经向他们解释了你提到的所有要点，但他们想要外观特性而不是功能。即使他们将数据导出到Excel中，他们也表示不会过滤任何内容。他们拒绝了最初的版本，该版本完全按照您所说的开发（合同和空间之间的链接，我也认为这很正常），因为它“杂乱无章”。但是，他们认为我是“专家”：https://www.youtube.com/watch?v=BKorP55Aqvg&spfreload=10 - Radu Gheorghiu

视频看起来相当准确。听起来你处于困境中，我在之前的公司遇到类似情况时，不得不向管理层解释，他们要么拥有正确但不太美观的数据，要么拥有美观但不正确的数据，两者不能兼得；毕竟“你是专家”，所以给出你的专业意见。我相信这是可能的，但说实话，如果他们每次都得逞，他们将会让你未来的生活变得很糟糕，他们应该尊重你的意见，解释局限性，从长远来看这将是值得的。 - Riller '-'

另外，SQL Server Reporting Services也可以为演示目的消除重复字段，但对于租户1仍将有4条记录，因为这是正确的，只是它会将其布局，以便您只在相关的第一行看到“租户1”。话虽如此，如果您像对adrianm所说的那样只能访问SQL Server，那么我不知道您安装SSRS的机会有多大，并且学习其使用时间，您的经理听起来很困难。 - Riller '-'

实际上，我们有一个内部构建的报告系统，类似于SSRS，因此甚至更多的限制适用（因为它是内部构建的，我们有“自定义的东西”，再次强调，不是我开发的，我只是被迫使用它）。而且由于我们有所有这些限制，我正试图导出数据的格式是我看到与报告的另一部分集成最容易的格式，该部分已经编写完毕。所以，无论如何，尽管我是“专家”，我仍然必须找到一种方法使“显示7行，其中4行用黑色墨水，3行用透明墨水”，但我不知道哪里可以获得透明墨水... - Radu Gheorghiu