MySQL внешняя замена соединения

Question

MySQL внешняя замена соединения

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

SELECT rwl.*, g.gameName, coalesce(payYes, 0) payYes, coalesce(payNo, 0) payNo FROM RestockWishList AS rwl, Games AS g
        LEFT JOIN
        (SELECT gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes, COUNT(if(prepaid='No', 1, NULL)) payNo FROM ReservationsBuy GROUP BY gameID) AS res
        ON res.gameID = g.gameID
        WHERE rwl.gameID = g.gameID;

Результаты запроса: gameID, количество, название игры, плати!, пайно

1, Четыре, Замок для все времена года, Ноль, 0

2, Два, Несколько акров снега, Ноль, 0

18, Четыре, Алхамбра, Ноль, 0

[3]}54, Два, Большое Ошеломление, Два, 0 По-видимому, решение этой проблемы заключается в использовании полного внешнего соединения вместо левого соединения, но MySQL не поддерживает эту функцию. Я потратил много часов, пытаясь перевести его в структуру профсоюза, но не могу заставить его работать правильно. Это самое близкое, что у меня есть:

SELECT rwl.*, res.gameID, res.payYes, res.payNo FROM RestockWishList rwl
LEFT JOIN
(SELECT gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes, COUNT(if(prepaid='No', 1, NULL)) payNo FROM ReservationsBuy GROUP BY gameID) AS res
        ON res.gameID = rwl.gameID
UNION
SELECT rwl.*, res.gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes, COUNT(if(prepaid='No', 1, NULL)) payNo FROM ReservationsBuy res
LEFT JOIN RestockWishList rwl ON rwl.gameID = res.gameID;

Результаты запроса: gameID, количество, gameID, payYes, payNo

1, 4, NULL, NULL, NULL

2, 2, NULL, NULL, NULL

18, 4, NULL, NULL, NULL

54, 2, 54, 2, 0

NULL, NULL, 30, 3, 1

(Извините, я не знаю, как красиво форматировать результаты таблицы запросов в StackOverflow.)

Я хочу, чтобы запрос отображался так, как я его изначально написал, только с пропущенными значениями из ReservationsBuy. Конкретная помощь, пожалуйста?

Таблицы:

CREATE TABLE IF NOT EXISTS RestockWishList (
gameID INT(6),
quantity INT(3) NOT NULL,
PRIMARY KEY (gameID),
FOREIGN KEY (gameID) REFERENCES Games(gameID) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS ReservationsBuy (
gameID INT(6),
customerEmail VARCHAR(25) NOT NULL,
customerName VARCHAR(25) NOT NULL,
dateReserved DATETIME NOT NULL,     #date customer files game reservation
datePurchased DATETIME,             #date Board and Brew restocks game
dateClaimed DATETIME,               #date customer physically claims game
prepaid ENUM('Yes', 'No') NOT NULL,
PRIMARY KEY (gameID, customerEmail),
FOREIGN KEY (gameID) REFERENCES Games (gameID) ON UPDATE CASCADE ON DELETE CASCADE);

Образец данные: RestockWishList:

GameID, количество

1, 4

2, 2

18, 4

[3]}54, 2

ReservationsBuy:

GameID, customerEmail, customerName, dateReserved, дата покупки, dateClaimed, prepaid

30, wonder@woman.com, Диана, 2015-04-24 14: 46: 05, НУЛЕВОЙ, НУЛЕВОЙ, Да

[3]}54, boggie@marsh.com, багги, 2015-04-24 14: 43: 32, НУЛЕВОЙ, НУЛЕВОЙ, Да [3]}54, manny@second.com, Мэнни, 2015-04-27 19: 48: 22, НУЛЕВОЙ, НУЛЕВОЙ, Да

43, old@mom.com, бабуля, 2015-04-23 22: 32: 03, НУЛЕВОЙ, НУЛЕВОЙ, Нет

Ожидаемый результат: gameID, количество, gameName, payYes, payNo

1, 4, Замок на все времена года, 0, 0

2, 2, несколько акров снега, 0, 0

18, 4, Альгамбра, 0, 0

30, 0, Arkham Horror, 1, 0

43, 0, Бананаграммы, 0, 1

54, 2, Big Boggle, 2, 0

(Таблица игр не особенно важна для этого запроса. Только релевантность в том, что и то и другое ReservationsBuy и RestockWishList связаны с играми по gameID)

3 5

mysql full-outer-join

3 ответа:

Я думаю, может быть, вам нужен такой запрос - не полное внешнее соединение:
select q.id, q.name, q.reservations, ifnull(q2.wishcount, 0) wishcount, q.payYes, q.payNo
  from (
    select g.*, count(rb.gameid) reservations, count(case when prepaid = 'Yes' then 1 end) payYes, count(case when prepaid = 'No' then 1 end) payNo
      from games g
        left join reservationsbuy rb
          on g.id = rb.gameid
      group by g.id
  ) q
  left join (
    select g.id, sum(quantity) wishcount
      from games g
        left join restockwishlist rwl
          on g.id = rwl.gameid
      group by g.id
  ) q2
  on q.id = q2.id;
Здесь есть демонстрационная версия, но суть ее в том, что для каждой игры в игровом столе она даст вам общее количество оговорок, количество из списка пожеланий, и мы используем условный счетчик, чтобы обеспечить количествоprepaid = yes, или prepaid = no. По сути, это просто объединение двух небольших запросов к общему gameid.

Если вы хотите, чтобы это включало фильтрацию по дате и т. д., Вам может потребоваться быть немного более ясно о том, как вы хотите, чтобы результаты работали или отображались

1

Хорошо. Итак, мы теперь, что всегда существует запись в game таблице, верно?

Затем начните свой FROM с этой таблицы, тогда вам просто нужно сделать LEFT JOIN для каждой таблицы следующим образом:
SELECT
   rwl.*
 , g.gameName
 , coalesce(payYes, 0) payYes
 , coalesce(payNo, 0) payNo
FROM
  Games AS g LEFT JOIN
    RestockWishList AS rwl ON rwl.gameID = g.gameID LEFT JOIN
      (
         SELECT
            gameID
          , COUNT(if(prepaid='Yes', 1, NULL)) payYes
          , COUNT(if(prepaid='No', 1, NULL)) payNo
         FROM
           ReservationsBuy
         GROUP BY gameID) AS res ON res.gameID = g.gameID
;
Как вы можете сказать, единственными изменениями были: начните FROM с Games таблицы и используйте LEFT JOIN, также удалите условие из WHERE и поместите его в LEFT JOIN

0

hrunting · Accepted Answer · 2015-04-29 02:38:01

Вы на правильном пути с использованием FULL OUTER JOIN, у вас просто неправильная реализация.

A FULL OUTER JOIN в MySQL можно рассматривать как UNION a LEFT JOIN и A RIGHT JOIN. В своем запросе вы пытаетесь приблизить это, рассматривая RIGHT JOIN часть логики как обратную LEFT JOIN из двух таблиц, но ваша первая часть не работает, потому что это не подвыборка с той же последовательностью GROUP BY, что и ваша первая LEFT JOIN.

Самое простое, что нужно сделать, это просто взять ваш первый запрос LEFT JOIN строфа, скопируйте ее во вторую строфу и замените LEFT JOIN на RIGHT JOIN, затем свяжите результаты с таблицей игр, например:
SELECT g.gameID, IFNULL(q.quantity, 0) AS quantity, g.gameName,
       IFNULL(q.payYes, 0) AS payYes, IFNULL(q.payNo, 0) AS payNo
  FROM games g
       INNER JOIN (
         SELECT IFNULL(rwl.gameID, res.gameID) AS gameID, rwl.quantity,
                res.payYes, res.payNo
           FROM RestockWishList rwl
                LEFT JOIN (
                   SELECT gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes,
                          COUNT(if(prepaid='No', 1, NULL)) payNo
                     FROM ReservationsBuy
                 GROUP BY gameID
                ) AS res ON res.gameID = rwl.gameID
          UNION
         SELECT IFNULL(rwl.gameID, res.gameID) AS gameID, rwl.quantity,
                res.payYes, res.payNo
           FROM RestockWishList rwl
                RIGHT JOIN (
                    SELECT gameID, COUNT(IF(prepaid='Yes', 1, NULL)) payYes,
                           COUNT(IF(prepaid='No', 1, NULL)) payNo
                      FROM ReservationsBuy
                  GROUP BY gameId
                ) AS res ON res.gameID = rwl.gameID
       ) AS q ON g.gameID = q.gameID
Результаты скрипки SQL