合并两个select语句的结果

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

我正在使用T-SQL和ASP.NET，而且我是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:

QUERY2：

 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

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

3 个解决方案

#1

You can use a 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.

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

所以

You can use a 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

#2

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.

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

#3

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）的Microsoft SQL Server（请参阅http://msdn.microsoft.com/en-us/library/ms190766.aspx），它们非常适合查询优化。所以我建议你帮忙施工：

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编写代码非常容易，您会对性能感到满意。

#1