Объединить две таблицы, которые различаются количеством строк в 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, но добавить "OverCount" и ODA из запроса 1 и "UnderCount" и " ODA " из запроса 2.

Желаемый Конечный Результат 

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" в этом запросе дает мне общее количество строк Брана. Использование "суммы" также приводит к неправильной информации. И, наконец, если я удалю " COUNT (" тогда мне нужно поставить #QueryTwo.Подсчет голосов в группе по которой дает мне следующие результаты: 

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 имеет имена отображения, которых нет в запросе 2, и наоборот. Делающий трудно присоединиться??

Если мне нужно уточнить больше, пожалуйста, дайте мне знать, мой мозг-это кашица.

2 2
sql sql-server multiple-tables

2 ответа:

Попробуйте использовать 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, nooflevels
2

Вы можете использовать полное соединение для получения результатов из обеих таблиц. Для получения дополнительной информации, пожалуйста, посетите 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.NoOfLevels
0