Блог

Главная - Вопросы по программированию - Слева Соедините несколько таблиц в одной главной таблице

Слева Соедините несколько таблиц в одной главной таблице

#sql #sql-server

Вопрос:

Я имею в виду упражнение по присоединению к sqlzoo, связанное здесь: https://sqlzoo.net/wiki/The_JOIN_operation

Я уже решил это, используя «СЛУЧАЙ, КОГДА», как указано в вопросе, но я просто пытаюсь сделать это по-другому.

ниже приведен мой код, который дал мне правильный ответ во всех столбцах, кроме столбца score2, он показывает тот же результат, что и score1, мне просто было интересно, какую ошибку я совершил в приведенном ниже коде.

Это приведенные таблицы

Это и есть целевой результат

  SELECT 
  MDATE, 
  TEAM1, 
  COUNT(X.MATCHID) AS SCORE1,
  TEAM2,
  COUNT(Y.MATCHID) AS SCORE2 
FROM GAME
LEFT JOIN GOAL X ON ID = X.MATCHID AND TEAM1 = X.TEAMID
LEFT JOIN GOAL Y ON ID = Y.MATCHID AND TEAM2 = Y.TEAMID
GROUP BY MDATE, TEAM1, TEAM2

Комментарии:

1. Примеры данных и ожидаемые результаты помогут нам помочь вам здесь.

2. Я также настоятельно рекомендую вам использовать псевдонимы всех ваших таблиц и квалифицировать все ваши столбцы; ID например, из какой таблицы?

3. Ваш вопрос должен быть автономным, не полагаться на внешние ссылки; как указано, вам нужно использовать псевдонимы всех таблиц и столбцов, чтобы он был легко читаем без двусмысленности; Я бы, вероятно, использовал здесь коррелированный подзапрос.

Ответ №1:

Вы неправильно понимаете, как COUNT это работает.

Он подсчитывает все ненулевые значения, переданные ему. И он не подсчитывает количество MATCHID в каждой таблице до объединения, он работает только после объединения.

Таким образом , конечный набор результатов после присоединения затем вводится в GROUP BY , и только после этого COUNT выполняется. Поскольку MATCHID значение не равно нулю, для обоих подсчетов получается один и тот же результат.

Вместо этого вам нужно предварительно сгруппироваться, используя или LEFT JOIN APPLY 

 SELECT 
  GAME.MDATE, 
  GAME.TEAM1, 
  G1.SCORE1,
  GAME.TEAM2,
  G2.SCORE2 
FROM GAME
LEFT JOIN (
    SELECT MATCHID, TEAMID, COUNT(*) SCORE1
    FROM GOAL
    GROUP BY MATCHID, TEAMID
) G1 ON GAME.ID = G1.MATCHID AND GAME.TEAM1 = G1.TEAMID
LEFT JOIN (
    SELECT MATCHID, TEAMID, COUNT(*) SCORE2
    FROM GOAL
    GROUP BY MATCHID, TEAMID
) G2 ON GAME.ID = G2.MATCHID AND GAME.TEAM2 = G2.TEAMID;

Поочередно

 SELECT 
  GAME.MDATE, 
  GAME.TEAM1, 
  G1.SCORE1,
  GAME.TEAM2,
  G2.SCORE2 
FROM GAME
OUTER APPLY (
    SELECT COUNT(*) SCORE1
    FROM GOAL G1
    WHERE GAME.ID = G1.MATCHID AND GAME.TEAM1 = G1.TEAMID
) G1
OUTER APPLY (
    SELECT COUNT(*) SCORE2
    FROM GOAL G2
    WHERE GAME.ID = G2.MATCHID AND GAME.TEAM2 = G2.TEAMID
) G2;

Ответ №2:

 WITH xGoalQ
AS
(
    SELECT MATCHID, TEAMID , COUNT(1) AS Score
    FROM GOAL
    GROUP BY MATCHID, TEAMID
)

SELECT 
  MDATE, 
  TEAM1, 
  X.Score AS SCORE1,
  TEAM2,
  Y.Score AS SCORE2 
FROM GAME
LEFT JOIN xGoalQ X ON ID = X.MATCHID AND TEAM1 = X.TEAMID
LEFT JOIN xGoalQ Y ON ID = Y.MATCHID AND TEAM2 = Y.TEAMID
ORDER BY MDATE, TEAM1, TEAM2

или

 SELECT 
  MDATE, 
  TEAM1, 
  (SELECT COUNT(1) FROM GOAL WHERE MATCHID = GAME.Id AND TEAMID = GAME.Team1) AS SCORE1,
  TEAM2,
  (SELECT COUNT(1) FROM GOAL WHERE MATCHID = GAME.Id AND TEAMID = GAME.Team2) AS SCORE2 
FROM GAME
ORDER BY MDATE, TEAM1, TEAM2
Метки: