sql - 合并两个 select 语句的结果

Question

我正在将 T-SQL 与 ASP.NET 和 C# 一起使用，而且我对 SQL 还很陌生。

我想知道如何合并两个查询的结果

查询1:

SELECT tableA.Id,  tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers
 FROM tableD RIGHT OUTER JOIN 
         [tableB] INNER JOIN
         tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id
 GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl

查询2:

 SELECT tableA.Id,  tableA.Name, COUNT([tableC].Id) AS NumberOfPlans
 FROM   [tableC] RIGHT OUTER JOIN
           tableA ON [tableC].tableAId = tableA.Id
 GROUP BY tableA.Id, tableA.Name

任何帮助将不胜感激。提前致谢

Answer 1

您可以使用Union .

这将在单独的行中返回查询结果。

首先，您必须确保两个查询返回相同的列。

然后你可以这样做:

SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS Number
FROM tableD 
RIGHT OUTER JOIN [tableB] 
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id 
GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl

UNION

SELECT tableA.Id, tableA.Name,  '' AS Owner, '' AS ImageUrl, '' AS CompanyImageUrl, COUNT([tableC].Id) AS Number
FROM 
[tableC] 
RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id GROUP BY tableA.Id, tableA.Name

正如已经提到的，两个查询返回完全不同的数据。您可能只想在两个查询都返回可被视为相似的数据时才执行此操作。

所以

您可以使用加入

两个查询之间是否共享一些数据。这会将两个查询的结果放入由 id 连接的单行中，这可能是您想要在这里做的更多事情...

你可以这样做:

SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers, query2.NumberOfPlans
FROM tableD 
RIGHT OUTER JOIN [tableB] 
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id 


INNER JOIN 
  (SELECT tableA.Id, COUNT([tableC].Id) AS NumberOfPlans 
   FROM [tableC] 
   RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id 
   GROUP BY tableA.Id, tableA.Name) AS query2 
ON query2.Id = tableA.Id

GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl