合并SQL server中行数不同的两个表。
我有两个表，我想要连接它们来创建最后一个表。
查询1
select DisplayName, Category, NoOfLevels
, count(Underoverestimate) as OverCount
, Avg(CaseDuration - EstDuration) as ODA
from DSU
where yearid between '2016' and '2018'
and underoverestimate = 'Over'
group by DisplayName, Category, nooflevels查询2
select DisplayName, Category, NoOfLevels
, count(Underoverestimate) as UnderCount
, Avg(CaseDuration - EstDuration) as ODA
from DSU
where yearid between '2016' and '2018'
and underoverestimate = 'Under'
group by DisplayName, Category, nooflevels查询%1结果
DisplayName|Category |NoOfLevels|OverCount|ODA
Bran, J. |Fusion |Single |2 |102.5
Bran, J. |Decompression| |1 |13
Caron, M. |Fusion |Multi |9 |88.444查询2结果
DisplayName|Category |NoOfLevels|UnderCount|ODA
Curry, S. |Fusion |Multi |2 |105
Bran, J. |Fusion |Single |1 |115.5
Bran, J. |Decompression| |4 |131
Caron, M. |Decompression| |5 |66我希望最终结果保留所有唯一的DisplayName、Catergory和NoOfLevels，但添加来自查询1的'OverCount‘和ODA以及来自查询2的'UnderCount’和'ODA‘。
想要的最终结果
DisplayName|Category |NoOfLevels|OverCount|ODA |UnderCount|ODA
Bran, J. |Fusion |Single |2 |102.5 |1 |115.5
Bran, J. |Decompression| |1 |13 |4 |131
Caron, M. |Decompression| | | |5 |66
Caron, M. |Fusion |Multi |9 |88.444 | |
Curry, S. |Fusion |Multi | | |5 |66为此，我尝试使用查询1和查询2创建临时表，然后创建一个新的select语句来报告我想要的数据。
Select #QueryOne.DisplayName, #QueryOne.Category,
#QueryOne.NoOfLevels, count(#QueryTwo.UnderCount) as UnderCount
from #QueryOne
join #QueryTwo
on #QueryOne.DisplayName = #QueryTwo.DisplayName
group by #QueryOne.DisplayName, #QueryOne.Category,
#QueryOne.NoOfLevels
order by #QueryOne.DisplayName, #QueryOne.Category,
#QueryOne.NoOfLevels我的结果是错误的。(我仍然在测试查询，所以我还没有包括我想要的所有列，但在测试中我注意到结果是错误的)
DisplayName|Category |NoOfLevels|UnderCount|
Bran, J. |Fusion |Single |6 |
Caron, M. |Fusion |Multi |9 |
Bran, J. |Decompression| |6 |
Curry, S. |Fusion |Multi |12 |
Caron, M. |Decompression| |9 |前3列看起来是正确的，但'UnderCount‘值不正确。此查询中的' count‘函数给出了Bran的总行数。使用'SUM‘也会导致错误的信息。最后，如果我删除了'COUNT(‘，那么我需要把#QueryTwo.UnderCount放入group中，这会给出以下结果：
DisplayName|Category |NoOfLevels|UnderCount|
Bran, J. |Fusion |Single |1 |
Bran, J. |Fusion |Single |2 |
Bran, J. |Decompression| |1 |
Bran, J. |Decompression| |2 |
Caron, M. |Decompression| |3 |
Caron, M. |Decompression| |2 |
Caron, M. |Fusion |Multi |3 |
Caron, M. |Fusion |Multi |1 |我试着通过stackoverflow寻找这个问题的答案，但没有发现类似的问题，我发现了很多关于连接两个表的问题，但它们的问题并不相同……我考虑过联合，但我似乎不能确定这是不是下一步的正确选择。我认为部分问题是查询1的DisplayNames不在查询2中，反之亦然。使其难以加入？？
如果我需要澄清更多，请让我知道，我的大脑是糊涂的。
回答 2
Stack Overflow用户
发布于 2017-08-05 04:45:55
尝试使用full join从两个表中获取所有唯一的DisplayName、Category、NoOfLevels行
select *
from (query1) t1
full join (query2) t2
on t1.DisplayName = t2.DisplayName
and t1.Category = t2.Category
and t1.NoOfLevels = t2.NoOfLevels另一种可能的解决方案是使用不带连接的条件聚合
select DisplayName, Category, NoOfLevels
, count(case when underoverestimate = 'Over' then Underoverestimate end) as OverCount
, count(case when underoverestimate = 'Under' then Underoverestimate end) as UnderCount
, Avg(case when underoverestimate = 'Over' then CaseDuration - EstDuration end) as ODA
, Avg(case when underoverestimate = 'Under' then CaseDuration - EstDuration end) as UDA
from DSU
where yearid between '2016' and '2018'
and underoverestimate IN ( 'Over' , 'Under' )
group by DisplayName, Category, nooflevelsStack Overflow用户
发布于 2017-08-05 04:52:00
您可以使用full join从两个表中获取结果。如需更多信息，请访问网址：https://www.w3schools.com/sql/sql_join_full.asp。
SELECT
*
FROM (SELECT
DisplayName,
Category,
NoOfLevels,
COUNT(Underoverestimate) AS OverCount,
AVG(CaseDuration - EstDuration) AS ODA
FROM DSU
WHERE yearid BETWEEN '2016' AND '2018'
AND underoverestimate = 'Over'
GROUP BY DisplayName,
Category,
nooflevels) a
FULL OUTER JOIN (SELECT
DisplayName,
Category,
NoOfLevels,
COUNT(Underoverestimate) AS UnderCount,
AVG(CaseDuration - EstDuration) AS ODA
FROM DSU
WHERE yearid BETWEEN '2016' AND '2018'
AND underoverestimate = 'Under'
GROUP BY DisplayName,
Category,
nooflevels) b
ON a.DisplayName = b.DisplayName
AND a.Category = b.Category
AND a.NoOfLevels = b.NoOfLevelshttps://stackoverflow.com/questions/45515156
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号