SQL 合并两个 select 语句的结果

combining results of two select statements

提问by ErnieStings

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

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

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

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

Query1:

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

Query2:

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

任何帮助将非常感激。提前致谢

回答by Mongus Pong

You can use a Union.

您可以使用Union。

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

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

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

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

Then you can do :

然后你可以这样做：

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.

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

SO

所以

You can use a Join

您可以使用 Join

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

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

You could do :

你可以这样做：

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

回答by Mark Byers

While it is possible to combine the results, I would advise against doing so.

虽然可以合并结果，但我建议不要这样做。

You have two fundamentally different types of queries that return a different number of rows, a different number of columns and different types of data. It would be best to leave it as it is - two separate queries.

您有两种根本不同的查询类型，它们返回不同的行数、不同的列数和不同类型的数据。最好保持原样 - 两个单独的查询。

回答by Oleg

Probably you use Microsoft SQL Server which support Common Table Expressions (CTE) (see http://msdn.microsoft.com/en-us/library/ms190766.aspx) which are very friendly for query optimization. So I suggest you my favor construction:

可能您使用了支持公共表表达式 (CTE)（请参阅http://msdn.microsoft.com/en-us/library/ms190766.aspx）的Microsoft SQL Server，它对查询优化非常友好。所以我建议你我喜欢的建筑：

WITH GetNumberOfPlans(Id,NumberOfPlans) AS (
    SELECT tableA.Id, COUNT(tableC.Id)
    FROM tableC
        RIGHT OUTER JOIN tableA ON tableC.tableAId = tableA.Id
    GROUP BY tableA.Id
),GetUserInformation(Id,Name,Owner,ImageUrl,
                     CompanyImageUrl,NumberOfUsers) AS (
    SELECT tableA.Id, tableA.Name, tableB.Username AS Owner, tableB.ImageUrl,
        tableB.CompanyImageUrl,COUNT(tableD.UserId),p.NumberOfPlans
    FROM tableA
        INNER JOIN tableB ON tableB.Id = tableA.Owner
        RIGHT OUTER JOIN tableD ON tableD.tableAId = tableA.Id
    GROUP BY tableA.Name, tableB.Username, tableB.ImageUrl, tableB.CompanyImageUrl
)
SELECT u.Id,u.Name,u.Owner,u.ImageUrl,u.CompanyImageUrl
    ,u.NumberOfUsers,p.NumberOfPlans
FROM GetUserInformation AS u
    INNER JOIN GetNumberOfPlans AS p ON p.Id=u.Id

After some experiences with CTE you will be find very easy to write code using CTE and you will be happy with the performance.

在使用 CTE 之后，您会发现使用 CTE 编写代码非常容易，并且您会对性能感到满意。