MySQL, где предложение с союзом Все получают неправильные результаты
Я предварю это, сказав, что я все еще очень много изучаю MySQL, и я абсолютно на том этапе, когда я знаю достаточно, чтобы быть опасным.
У меня есть база данных с данными для ведения счета спортивной лиги. Мы записываем выигрыши/проигрыши как 1 или ноль очков. Есть ночь, в которой задействована двойная игра (это означает, что игроки играют дважды за одну ночь для 2 разных форматов). Мои данные структурированы так (просто образец, у меня есть сотни строк в разных форматах):
| Я БЫ | FID | ВК | Тип | Домашняя команда | Гости | ВЧ1 | ВЧ2 | AF1 | АФ2 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 44 | 1 | PL | ТМ1 | ТМ2 | 1 | 0 | 0 | 1 |
| 2 | 44 | 1 | PL | ТМ3 | ТМ4 | 0 | 0 | 1 | 1 |
| 3 | 44 | 2 | PL | ТМ2 | ТМ3 | 1 | 1 | 0 | 0 |
| 4 | 44 | 2 | PL | ТМ4 | ТМ1 | 0 | 1 | 1 | 0 |
| 5 | 44 | 3 | PL | ТМ3 | ТМ1 | 999 | 0 | 999 | 1 |
| 6 | 44 | 3 | PL | тм2 | ТМ4 | 1 | 0 | 0 | 1 |
Где 999 используется как кодовый номер, чтобы мы знали, что матч еще не был сыгран или протокол не передан нам для ведения учета. (Я использую PHP, чтобы вызвать их на веб-сайт, чтобы пользователи могли видеть, что происходит, и я использую оператор IF для преобразования этого 999 в «TBD» на веб-сайте)
Я могу получить баллы по формату 1 и формату 2 по отдельности и получить список, но когда я пытаюсь собрать их вместе и получить общий балл, я получаю неверный счет. Я знаю, что ошибка связана с моим предложением WHERE, но я ломал голову, пытаясь заставить его работать правильно, и я думаю, что мне просто нужно дополнительно посмотреть на это.
Мой текущий SQL-запрос выглядит следующим образом:
SELECT Team,
SUM(TotalF1) AS TotalF1,
SUM(TotalF2) AS TotalF2,
SUM(TotalF1+TotalF2) AS Total
FROM ( ( SELECT HomeTeam AS Team,
HF1 AS TotalF1,
HF2 AS TotalF2
FROM tbl_teamscores
WHERE FID = 44
AND Type = 'PL'
AND HF1 != 999
AND HF2 != 999 )
UNION ALL
( SELECT AwayTeam,
AF1,
AF2
FROM tbl_teamscores
WHERE FID = 44
AND Type = 'PL'
AND AF1 != 999
AND AF2 != 999 )
) CC
GROUP BY Team
ORDER BY Total desc, Team ASC;
Однако я получаю неверные итоги, и я знаю, что причина в этих 999 обозначениях, поскольку предложение WHERE пропускает ВСЕ строки, где домашний или выездной счет соответствует 999.
Я попытался разделить его на 4 отдельных оператора Select и объединить их, но я просто получаю сообщение об ошибке, когда делаю это. Я также пытался использовать Inner Join, но MySQL это тоже не нравится.
Редактировать для добавления DBFiddle с табличными данными и запросами реального мира: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1d4d090b08b8280e734218ba32db6d88 Пример проблемы можно наблюдать при просмотре данных для игрока 10. Общее количество должно быть 13, но я получаю только 12.
Любые предложения были бы очень полезны.
Заранее спасибо!
Вот мои фактические данные (имена игроков изменены, чтобы скрыть личности): dbfiddle.uk/… Если вы посмотрите на Игрока 10, в Total8 у него 7 очков, в TotalLO у него 6, что должно дать ему в общей сложности 13 очков. Но когда я пытаюсь сложить их вместе, он показывает только 6 баллов для Total8, и я на 99,9% уверен, что это потому, что на 6-й неделе мне не хватает очков для него в формате LO.
Ответы 1
Вы можете использовать условную агрегацию:
SELECT Team,
SUM(CASE WHEN Total8 <> 999 THEN Total8 END) AS Total8,
SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS TotalLO,
SUM(CASE WHEN Total8 <> 999 THEN Total8 END) + SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS Total
FROM (
SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
UNION ALL
SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
) CC
GROUP BY Team
ORDER BY Team ASC;
или:
SELECT Team,
SUM(NULLIF(Total8, 999)) AS Total8,
SUM(NULLIF(TotalLO, 999)) AS TotalLO,
SUM(NULLIF(Total8, 999)) + SUM(NULLIF(TotalLO, 999)) AS Total
FROM (
SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
UNION ALL
SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
) CC
GROUP BY Team
ORDER BY Team ASC;
Если вы получаете nulls в результатах, вы также должны использовать COALESCE():
SELECT Team,
COALESCE(SUM(NULLIF(Total8, 999)), 0) AS Total8,
COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS TotalLO,
COALESCE(SUM(NULLIF(Total8, 999)), 0) + COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS Total
FROM (
SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
UNION ALL
SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL'
) CC
GROUP BY Team
ORDER BY Team ASC;
Смотрите демо.
Благодарю вас! Я не знал ни об одном из этих вариантов, но мне они все нравятся. Я играл со всеми из них, но решил использовать первый вариант условной агрегации, так как он показался мне наиболее разумным в первом тесте.
Вы должны создать пример ввода и вывода, чтобы было ясно и воспроизводимо, что такое ввод и желаемый вывод, хорошее место для создания примера: dbfiddle.uk/?rdbms=mysql_8.0