合并两个 select 语句的结果 [英] combining results of two select statements

问题描述

我正在将 T-SQL 与 ASP.NET 和 c# 结合使用，而且我对 SQL 还是很陌生.

I'm using T-SQL with ASP.NET, and c# and i'm pretty new to SQL.

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

I was wondering how i could combine the results of two queries

查询 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

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

Any help would be much appreciated. Thanks in advance

推荐答案

您可以使用 联盟.

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

This will return the results of the queries in separate rows.

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

First you must make sure that both queries return identical columns.

然后你可以这样做:

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

如前所述，两个查询返回的数据完全不同.如果两个查询返回的数据可能相似，您可能只想这样做.

As has been mentioned, both queries return quite different data. You would probably only want to do this if both queries return data that could be considered similar.

所以

您可以使用加入

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

If there is some data that is shared between the two queries. This will put the results of both queries into a single row joined by the id, which is probably more what you want to be doing here...

你可以这样做:

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

这篇关于合并两个 select 语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！