Получить нулевые записи в SQL
У меня следующий запрос:
SELECT c.name as clientName, p.id as projectId, p.name as projectName, p.rate, u.name as userName, sum(w.duration) as workedHours
FROM Project p, User u, Worklog w, Client c
WHERE w.user_id = u.id AND w.project_id = p.id AND p.client_id = c.id
GROUP BY p.id, u.id
который возвращает проекты, клиентов, почасовую ставку и отработанные часы.
Что нужно изменить, чтобы вернуть также проекты, где workedHours is equal with 0?
Потому что этот запрос возвращает только те записи, где workedHours is not 0.
Спасибо за ваше время.
Этот запрос вообще рабочий, ведь в GROUP BY всего два столбца, в SELECT их больше двух?
@EminMesic да, он работает хорошо (возвращает все записи, в которых находит данные) даже без третьего условия для GROUP BY
@underscore_d спасибо за информацию, на самом деле мало что знаю о левом/правом/внутреннем соединении, но это хороший повод начать и изучить их, спасибо
@Emin Mesic: Это нормально в соответствии со стандартным SQL. Группируя по идентификатору пользователя и идентификатору проекта, мы можем безопасно выбирать данные пользователя и проекта, такие как имена и рейтинг. И поскольку в каждом проекте есть только один клиент, мы также можем выбрать данные клиента. Это называется функциональной зависимостью. Однако некоторые СУБД не поддерживают это и требуют добавления столбцов данных в предложение GROUP BY.
@Vlad Popa: соединения, разделенные запятыми, в том виде, в каком вы их используете, на самом деле являются пережитком 1980-х годов. Явные соединения (INNER JOIN, LEFT OUTER JOIN и т. д.) стали стандартом SQL в 1992 году. Так что да, полезно изучить их и отказаться от использования соединений с разделителями-запятыми.
Ответы 2
Проблема в том, что ни одна строка в рабочем журнале не может быть объединена, и что ваше условие в предложении WHERE удаляет любую строку, не связанную с рабочим журналом.
Решение 1. Использование LEFT JOIN
Вместо этого использование левого соединения решит вашу проблему.
SELECT c.name as clientName, p.id as projectId, p.name as projectName, p.rate, u.name as userName, coalesce(sum(w.duration), 0) as workedHours
FROM Project p, User u, Client c
LEFT JOIN Worklog w ON w.project_id = p.id AND w.user_id = u.id
WHERE p.client_id = c.id
GROUP BY p.id, u.id
Кстати, ваш запрос подозрительный в других аспектах. Например, c.name находится в предложении SELECT, но не в предложении GROUP BY. Насколько я понимаю, вы используете MySQL, единственную из известных мне СУБД, которая разрешает такие запросы. Возможно, вам следует подумать о добавлении извлеченных столбцов в предложение GROUP BY.
Решение 2. Использование только ANSI JOIN
Как указывает underscore_d, вы можете полностью избежать соединений в старом стиле и предпочтительнее использовать следующий запрос:
SELECT
c.name as clientName,
p.id as projectId,
p.name as projectName,
p.rate,
u.name as userName,
coalesce(sum(w.duration), 0) as workedHours
FROM Project p
CROSS JOIN User u
INNER JOIN Client c ON p.client_id = c.id
LEFT JOIN Worklog w ON w.project_id = p.id AND w.user_id = u.id
GROUP BY c.name, p.id, p.name, p.rate, u.id, u.name
Решение 3. Использование подзапроса
Другим решением является использование подзапроса, который позволит вам полностью удалить предложение GROUP BY и получить более управляемый запрос, если вам когда-нибудь понадобится получить больше информации. Лично мне не нравятся длинные списки столбцов в предложении GROUP BY.
SELECT
c.name as clientName,
p.id as projectId,
p.name as projectName,
p.rate,
u.name as userName,
(SELECT SUM(duration) FROM Worklog WHERE project_id = c.id AND user_id = u.id) as workedHours
FROM Project p
CROSS JOIN User u
INNER JOIN Client c ON p.client_id = p.id
Действительно, но мы также должны посоветовать не использовать where и для внутренних соединений; вместо этого используйте inner join. Использование where для «присоединения» устарело с 1992 года?
Я не знал, что это устарело. Я думал, что это просто вопрос стиля. В любом случае, я добавил ваше предложение к моему ответу. Спасибо, что указали на это.
@FabianPijcke Я действительно мало что знаю о SQL, но последний написанный вами запрос на самом деле не возвращает записи, даже не близкие, как ожидалось (но я действительно не знаю, почему, поэтому я не могу указать, где или если была ошибка). Кроме того, спасибо за ваше время
Действительно, я написал p вместо c :-) Позвольте мне исправить это, пожалуйста! И спасибо за улов
@FabianPijcke Спасибо, это второе решение действительно сработало для меня, я не уделял должного внимания псевдонимам таблиц и думал о каком-то объединении или о чем-то еще. Спасибо за ваше время!
@FabianPijcke У меня есть вопрос. Если я попытаюсь добавить пункт WHERE, например, для w.date BETWEEN dateA AND dateB, он должен быть перед GROUP BY или где-то в JOIN с AND w.date BETWEEN dateA AND dateB? Ответ для новичков вроде меня: в этой строке LEFT JOIN Worklog w ON w.project_id = p.id AND w.user_id = u.id я добавил условие для даты, и это сработало
Вы должны использовать стандартные соединения ANSI и использовать LEFT JOIN в таблице worklog, и в конечном итоге вы должны использовать LEFT JOIN в таблице user следующим образом:
SELECT C.NAME AS CLIENTNAME,
P.ID AS PROJECTID,
P.NAME AS PROJECTNAME,
P.RATE,
U.NAME AS USERNAME,
SUM(W.DURATION) AS WORKEDHOURS
FROM PROJECT P
JOIN CLIENT C
ON P.CLIENT_ID = C.ID
LEFT JOIN WORKLOG W
ON W.PROJECT_ID = P.ID
LEFT JOIN USER U
ON W.USER_ID = U.ID
GROUP BY P.ID,
U.ID;
Просто используйте правильные
left join, а не то древнееWHEREчудовище. Тогда строки безWorklogбудут иметьnullв своих полях.