Форум пользователей MySQL

wine-time · 10.10.2013 16:09:57

Не выводит записи, где count = 0. Группировка есть, где я не прав?

SELECT
catalogs.id,
COUNT (DISTINCT comments.id) AS comments
FROM catalogs
LEFT JOIN comments ON catalogs.id = comments.catalog
WHERE catalogs.removed = 0 AND comments.removed = 0
GROUP BY catalog.id
ORDER BY catalog.id

Отредактированно wine-time (10.10.2013 16:10:37)

vasya · 10.10.2013 16:58:02

Приведите пример с тестовыми данными на которых видна проблема.

wine-time · 11.10.2013 10:13:31

SELECT

    catalogs.id,

    COUNT(DISTINCT comments.id) AS comments

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0

GROUP BY catalogs.id

ORDER BY catalogs.id

Маленькая база с 2 таблицами здесь https://dl.dropboxusercontent.com/u/36526176/test.sql
Если будет лень экспортировать, то в первой таблице просто catalog{id, name};
а вторая выглядит так: https://dl.dropboxusercontent.com/u/365 … able_2.png

Я получаю:
id comments
1 1
2 1
4 1
6 3

Т.е. не выводит те, где подсчёт дал бы 0

Отредактированно wine-time (11.10.2013 10:15:46)

vasya · 11.10.2013 12:55:19

Так вы сами отсекаете их условием WHERE comments.removed = 0

Для наглядности выполните на тестовых значениях:

SELECT

*

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog;

и

SELECT

*

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0;

Возможно вам нужно:

SELECT

    catalogs.id,

    COUNT(DISTINCT comments.id) AS comments

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0  or removed is null

GROUP BY catalogs.id

ORDER BY catalogs.id;

wine-time · 11.10.2013 13:04:29

Т.е. вместо условия на count, я поставил условие на всю выборку..
Спасибо вам большое.

wine-time · 14.10.2013 11:33:51

UPD: простите, снова вопрос. прочитал про is null, обрадовался новым знаниям.
Вы были правы, мне нужный последний запрос,

SELECT

    catalogs.id,

    COUNT(DISTINCT comments.id) AS comments

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0  or removed is null

GROUP BY catalogs.id

ORDER BY catalogs.id;

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

SELECT
catalogs.id AS id,
COUNT(DISTINCT t.id) AS total,
COUNT(DISTINCT c.id) AS closed
FROM catalogs
LEFT JOIN comments t ON catalogs.id = t.catalog
LEFT JOIN comments c ON catalogs.id = c.catalog
WHERE catalogs.removed = 0
AND ( t.removed = 0 OR t.removed is NULL )
AND ( c.removed = 0 OR c.removed is NULL )
AND ( c.is_open = 0 OR c.removed is NULL )
GROUP BY catalogs.id
ORDER BY catalogs.id;

Но такой запрос отдаёт те, где:
• вообще нет comments (т.е. найдено 0)
• есть хотя бы 1, где comments.is_open = 0

Из выдачи убраны те каталоги, где подсчёт дал больше нуля, но среди них нет удовлетворяющего c.is_open = 0

Что я упустил?

Отредактированно wine-time (14.10.2013 11:40:48)

vasya · 14.10.2013 13:23:45

В корне неправы. Выполните:

SELECT * FROM catalogs

LEFT JOIN comments t ON catalogs.id = t.catalog

LEFT JOIN comments c ON catalogs.id = c.catalog;

и

SELECT * FROM catalogs

LEFT JOIN comments t ON catalogs.id = t.catalog

LEFT JOIN comments c ON catalogs.id = c.catalog

WHERE catalogs.removed = 0

    AND ( t.removed = 0 OR t.removed is NULL )

    AND ( c.removed = 0 OR c.removed is NULL )

    AND ( c.is_open = 0 OR c.removed is NULL );

Уже на первом запросе вы можете увидеть, что двойной join даст неправильный результат для общего числа комментариев (а именно их возведение в квадрат).
WHERE выполняется раньше GROUP BY, добавив условие, вы теряете нужные вам данные.

Если правильнро понял, то вам нужно:

SELECT

    catalogs.id,

    COUNT(DISTINCT comments.id) AS comments,

    SUM(IF(comments.c_is_open=0 or comments.removed is null),1,0) as closed

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0  or removed is null

GROUP BY catalogs.id

ORDER BY catalogs.id;

wine-time · 14.10.2013 17:27:25

Спасибо, что тратите своё время.
Сейчас медленно понимаю, что mysql просто соберёт для меня огромную таблицу и я на неё пытаюсь наложить взаимоисключающие условия.

В запросе пишет про ошибку на четвертой строке. Логику запроса понял, но посмотрите, пожалуйста, в чём может быть ошибка (я с агрегатными фунциями и условиями не работал пока).

Мне ещё предложили такой вариант, работоспособный:

SELECT

    catalogs.id AS id,

    COUNT(DISTINCT t.id) AS total,

    COUNT(DISTINCT c.id) AS closed

FROM catalogs

LEFT JOIN comments t ON catalogs.id = t.catalog AND t.removed = 0

LEFT JOIN comments c ON catalogs.id = c.catalog AND c.removed = 0 AND ( c.is_open = 0 OR c.is_open is NULL )

WHERE catalogs.removed = 0

GROUP BY catalogs.id

ORDER BY catalogs.id;

Но вот как перенесли условия из WHERE в ON я не очень понимаю. Вы не раскроете идею?

vasya · 14.10.2013 17:58:23

wine-time написал:
В запросе пишет про ошибку на четвертой строке. Логику запроса понял, но посмотрите, пожалуйста, в чём может быть ошибка (я с агрегатными фунциями и условиями не работал пока).

Скобки потерял, нужно
SUM(IF((comments.c_is_open=0 or comments.removed is null),1,0)) as closed

wine-time написал:
Но вот как перенесли условия из WHERE в ON я не очень понимаю. Вы не раскроете идею?

Для наглядности выполните этот запрос бех группировки, т.е.

SELECT * FROM catalogs

LEFT JOIN comments t ON catalogs.id = t.catalog AND t.removed = 0

LEFT JOIN comments c ON catalogs.id = c.catalog AND c.removed = 0 AND ( c.is_open = 0 OR c.is_open is NULL )

WHERE catalogs.removed = 0;

Условие по которому связываются таблицы может быть не единственным. Здесь используется то, что сначала выполняется связывание (условия в части ON), потом WHERE.

wine-time · 14.10.2013 21:05:24

А запрос теперь работает, но выводит все: 0, закрытых: 1
Орфографию я пробовал поправить по логике, всё равно неправильно отрабатывает..

Отредактированно wine-time (14.10.2013 21:06:01)

vasya · 14.10.2013 21:24:30

Запрос покажите.

wine-time · 14.10.2013 21:39:34

Который я просил посмотреть на 4 строке, всё тот же. Я орфографические ошибки убрал только.

SELECT

    catalogs.id,

    COUNT(DISTINCT comments.id) AS comments,

    SUM( IF((comments.is_open = 0 OR comments.is_open IS NULL),1,0) ) AS closed

FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0  or comments.removed IS NULL

GROUP BY catalogs.id

ORDER BY catalogs.id;

Отредактированно wine-time (14.10.2013 21:42:00)

vasya · 14.10.2013 21:44:14

Возвращаемся к началу, тестовые данные приведите.

wine-time · 14.10.2013 21:50:18

И правда к началу. Так пойдёт или накидат тестовую и дать дамп?

Каталоги
id
1
2
3
4
5

Что-то ещё, пускай комментарии
id catalog removed is_open
1 1 1 1
2 1 1 1
3 1 0 0
4 3 0 1
5 4 0 1
6 4 0 0
7 4 1 0
8 4 0 1

UPD, если кому нужно, нашёл использование с таким синтаксисом

SELECT count(id),
SUM(hour) as totHour,
SUM(case when kind = 1 then 1 else 0 end) as countKindOne

Отредактированно wine-time (14.10.2013 22:05:28)

vasya · 14.10.2013 22:04:35

+------+----------+--------+

| id   | comments | closed |

+------+----------+--------+

|    1 |        1 |      1 |

|    2 |        0 |      1 |

|    3 |        1 |      0 |

|    4 |        3 |      1 |

|    5 |        0 |      1 |

+------+----------+--------+

wine-time · 14.10.2013 22:06:49

Да, я как раз про 2 и 5 каталоги.
comments: 0
closed: 1

Если что, нашёл использование if (обновил предыдущий комментарий), без вас не дошёл бы до идеи, спасибо большое за ответы.

Отредактированно wine-time (14.10.2013 22:08:06)

vasya · 14.10.2013 22:18:40

Выполните запрос без группировки, сразу станет понятна причина:

SELECT * FROM catalogs

LEFT JOIN comments ON catalogs.id = comments.catalog

WHERE comments.removed = 0  or comments.removed IS NULL

ORDER BY catalogs.id;

А еще сравните варианты с:
COUNT(DISTINCT comments.id) AS comments,
и
COUNT(*) AS comments,

wine-time · 15.10.2013 11:14:48

Не помогли мне запросы понять что-либо
Для меня логика топика строится так:
• описал проблему, получил пример запроса
• запрос не работает, спросил в чем ошибка, получил ответ
• запрос заработал, но выдает некорректную выборку
• спросил об этом, снова попросили описать ситуацию
• описал ситуацию, получил несколько запросов, которые для меня должны что-то прояснить

Спасибо вам за идею использования агрегации, но уж слишком вы усложняете процесс поиска ответа.

vasya · 15.10.2013 13:30:43

Каков вопрос, таков ответ. Вы не описываете проблему, и я могу лишь догадываться что вам нужно, а это (догадываться) не моя сильная сторона.

По последнему, вы говорите, что выборка (comments: 0; closed: 1) некорректна. Почему? Неясно. Можно лишь предположить, что вам нужно
COUNT(*) AS comments,
вместо
COUNT(DISTINCT comments.id) AS comments,

P.S. Если бы описали какую выборку хотите получить на тестовых данных и почему она должна быть такой, то и ответ вас вероятно бы устроил сразу.

Форум пользователей MySQL

#1 10.10.2013 16:09:57

select count() where, где count = 0

#2 10.10.2013 16:58:02

Re: select count() where, где count = 0

#3 11.10.2013 10:13:31

Re: select count() where, где count = 0

#4 11.10.2013 12:55:19

Re: select count() where, где count = 0

#5 11.10.2013 13:04:29

Re: select count() where, где count = 0

#6 14.10.2013 11:33:51

Re: select count() where, где count = 0

#7 14.10.2013 13:23:45

Re: select count() where, где count = 0

#8 14.10.2013 17:27:25

Re: select count() where, где count = 0

#9 14.10.2013 17:58:23

Re: select count() where, где count = 0

wine-time написал:

wine-time написал:

#10 14.10.2013 21:05:24

Re: select count() where, где count = 0

#11 14.10.2013 21:24:30

Re: select count() where, где count = 0

#12 14.10.2013 21:39:34

Re: select count() where, где count = 0

#13 14.10.2013 21:44:14

Re: select count() where, где count = 0

#14 14.10.2013 21:50:18

Re: select count() where, где count = 0

#15 14.10.2013 22:04:35

Re: select count() where, где count = 0

#16 14.10.2013 22:06:49

Re: select count() where, где count = 0

#17 14.10.2013 22:18:40

Re: select count() where, где count = 0

#18 15.10.2013 11:14:48

Re: select count() where, где count = 0

#19 15.10.2013 13:30:43

Re: select count() where, где count = 0

Board footer