Верните 0, если условие не совпадает

ProgramBox

Верните 0, если условие не совпадает

Post author:admin
Запись опубликована:12 января, 2022
Post category:Вопросы по программированию

#mysql #join #group-by #sum #where-clause

Вопрос:

У меня есть футбольный матч, где люди могут предсказывать матчи. Они могут дать матчу 1, если они думают, что победит команда хозяев, 2, если победит клуб гостей, и 3 за ничью. Если они правильно предсказывают матч, они получают очко. Таблицы в моей базе данных выглядят следующим образом:

Таблица совпадений

 
| id  | home     | away      | result | round_id
| --- | ---------| -------- -| -------|----------|
| 1   | id club 1| id club 2 | 1      | id round 1 
| 2   | id club 5| id club 4 | 3      | id round 1
| 3   | id club 8| id club 5 | 1      | id round 2

Table predictions

| prediction | user_id      | match_id |
| -------- | -------------- | -------- |
| 1        | id user 1      | id match 1 
| 3        | id user 1      | id match 2
| 2        | id user 1      | id match 3

Изначально я хотел рассчитать оценку на PHP, но я думаю, что это также должно быть возможно только через MySQL. Поэтому я кое-что попробовал и пришел со следующим запросом:

 SELECT Count(*) AS points,
       username,
       round_id
FROM   predictions
       LEFT JOIN matches
              ON predictions.match_id = matches.id
       INNER JOIN users
               ON predictions.user_id = users.id
WHERE  predictions.prediction = matches.result
GROUP  BY username,
          round_id
ORDER  BY points DESC,
          username ASC

Запрос правильно рассчитывает балл за раунд для каждого пользователя, единственная проблема заключается в том, что если участник не имеет никаких прав в игровом раунде, он вообще не появится в списке. Есть ли у кого-нибудь идеи, что сделать, чтобы также включить участника в список, если у него 0 баллов? В переводе на таблицы, упомянутые выше, раунд 2 не вернет запрос, поскольку единственное совпадение в нем было неверно предсказано. Однако я действительно хочу этого, и чтобы раунд 2 был возвращен со счетом 0.

Результат, которого я хочу:

 | points   | username       | round_id|
| -------- | -------------- | --------|
| 2        | John           | 1 
| 0        | John           | 2

Результат, который у меня сейчас есть:

 | points   | username       | round_id|
| -------- | -------------- | --------|
| 2        | John           | 1

1. Ваш вопрос немного сбивает с толку. Попробуйте отредактировать и уточнить, что вы имеете в виду? Вы имели в виду, что если предсказание первого раунда неверно, то предсказание второго раунда бесполезно, даже если оно правильное??? И о показе оценки 0 , если вы просто удалите WHERE утверждение и условие, оно покажет всех пользователей и прогноз. (На самом деле это WHERE утверждение является причиной фильтрации, которой вы не желаете)

2. Мне нужно «общее» количество очков за каждый раунд для каждого пользователя. Также, когда пользователь не получает ни одного очка в раунде, это 0 очков. Когда я удаляю то, ГДЕ пользователь получает очки за каждый матч, независимо от того, верен ли прогноз.

3. Просто удалите WHERE утверждение и условие, чтобы вы, вероятно, получили желаемый результат.

4. Не LEFT JOIN matches должно быть OUTER JOIN matches , если вы хотите получить все результаты?

5. @kmoser С ВНЕШНИМИ СОЕДИНЕНИЯМИ, к сожалению, я получаю тот же результат. К счастью, решение форпаса работает для меня.

Ответ №1:

Пункт WHERE :

 WHERE  predictions.prediction = matches.result

отфильтровывает любые неправильные прогнозы, но даже если вы удалите их, агрегатная функция COUNT(*) также будет учитывать неправильные прогнозы.

Присоединяйтесь и группируйтесь вот так:

 SELECT SUM(p.prediction = m.result) AS points,
       u.username,
       m.round_id
FROM users u 
INNER JOIN predictions p ON p.user_id = u.id
INNER JOIN matches m ON m.id = p.match_id
GROUP BY u.id, u.username, m.round_id
ORDER  BY points DESC, u.username ASC;

Агрегатная функция SUM() суммирует логические выражения prediction = result , которые оцениваются как 1 для true и 0 для false .

Смотрите демонстрацию.