我正在使用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].CompanyImageUrl
QUERY2：
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
任何帮助将不胜感激.
提前致谢
解决方法
您可以使用
Union.
这将以单独的行返回查询的结果.
首先,您必须确保两个查询返回相同的列.
那你可以做：
SELECT tableA.Id,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].CompanyImageUrl UNION SELECT tableA.Id,'' 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
如前所述,两个查询返回完全不同的数据.如果两个查询返回可能被认为是相似的数据,您可能只想这样做.
所以
您可以使用Join
如果两个查询之间共享一些数据.这将把这两个查询的结果都放在id中加入的单个行中,这可能是你想在这里做的更多的事情
你可以做：
SELECT tableA.Id,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].CompanyImageUrl
