Трудности с SQL-запросом и COUNT

Спасибо в Advnace за чтение этого. Я не очень хорошо разбираюсь в SQL, поэтому прошу прощения за любую глупую ошибку...

Вот сделка, у меня есть четыре таблицы (для простоты я собираюсь дать только основные поля и зависимости между таблицами):

  1. Компания: companyId, companyName
  2. Пользователь: userId, userName
  3. Project: projectId, projectUserId, projectCompanyId, projectDate
  4. Исследование: studyProjectId

Зависимости таковы:

  • Проект для клиента (projectUserId) и выполняется компанией (projectCompanyId)
  • Для одного и того же проекта может быть много исследований, но каждое исследование предназначено для одного проекта (studyProjectId)

Вот запрос, который я хотел бы написать, но сейчас он не работает:

SELECT
    project.projectId,
    company.companyName,
    user.userName,
    COUNT( study.studyId ) AS numberStudies
FROM project, company, user, study
WHERE company.companyId = project.projectCompanyId,
AND user.userId = project.projectUserId,
AND study.studyProjectId = project.projectId
ORDER BY company.companyId, user.userId, project.projectDate;

Возвращает одну запись, для которой количество записей равно общему количеству исследований. Если я удалю COUNT от SELECT тогда я получу желаемый результат, но без столбца numberStudies (конечно). Надеюсь, вы понимаете, что я пытаюсь получить, что я здесь делаю не так?

Еще раз спасибо заранее:)

РЕДАКТИРОВАТЬ: Если возможно, я хотел бы запрос показывать записи, даже когда numberStudies это 0.

Источник

2 ответа

Решение

Как и в комментариях, вам нужно GROUP BY предложение, когда вы хотите получить агрегированные результаты (как вам кажется: "Количество исследований на проект, компанию и пользователя"). Итак, первое, что нужно сделать, это добавить:

GROUP BY project.projectId, company.companyName, user.userName

Обратите внимание, что три столбца - это именно те три, которые у вас есть (неагрегированные) в SELECT список.

SELECT
    project.projectId,
    company.companyName,
    user.userName,
    COUNT(study.studyId) AS numberStudies
FROM project, company, user, study
WHERE company.companyId = project.projectCompanyId,
  AND user.userId = project.projectUserId,
  AND study.studyProjectId = project.projectId
GROUP BY project.projectId, company.companyName, user.userName
ORDER BY company.companyId, user.userId, project.projectDate ;

Это покажет, что вы хотите, но есть еще несколько проблем:

  • Во-первых, вы используете старый (SQL-89) синтаксис неявных объединений с условиями в WHERE пункт. Этот синтаксис не является устаревшим, но новый (ну, 20 лет SQL-92) синтаксис с JOIN Ключевое слово имеет несколько преимуществ.
  • Мы можем добавить псевдонимы для таблиц для удобства чтения.
  • Может быть две компании или пользователи с одинаковыми именами, поэтому мы должны группировать по их идентификаторам, а не только по именам.
  • Одно из преимуществ явного JOIN Синтаксис заключается в том, что легко получить результаты, когда нет строк для объединения (как вы хотите показать, когда нет исследований для проекта). Просто LEFT JOIN study Таблица.

Итак, запрос становится:

    SELECT
        p.projectId,
        c.companyName,
        u.userName,
        COUNT(s.studyId) AS numberStudies
    FROM 
        project AS p
      JOIN 
        company AS c  ON c.companyId = p.projectCompanyId
      JOIN 
        user AS u     ON u.userId = p.projectUserId
      LEFT JOIN 
        study AS s    ON s.studyProjectId = p.projectId
    GROUP BY 
        c.companyId,
        u.userId,
        p.projectId, 
        c.companyName,  u.userName
    ORDER BY 
        c.companyId, 
        u.userId, 
        p.projectDate ;
Источник

Вам, вероятно, нужно левое соединение между учебой и проектом

Источник
Другие вопросы по тегам