Блог

У меня есть таблица сведений о больницах, связанных с ней сведений о подразделениях, типы работников (персонал) в разных таблицах и информация об их заработной плате.

Я хочу извлечь следующее для каждой больницы: среднее значение и сумму зарплат, количество медсестер, количество врачей-исследователей и количество коек во всех отделениях конкретной больницы.

Я создал это представление всей информации о заработной плате работников:

     CREATE VIEW workers AS
    SELECT hospcod, docsal as sal, 'treatdoc' as typework
    FROM doc NATURAL JOIN treatdoc NATURAL JOIN dept
    UNION
    SELECT hospcod, nursal, 'nurse'
    FROM nurse  NATURAL JOIN dept
    UNION
    SELECT hospcod, docsal, 'rsrchdoc'
    FROM doc NATURAL JOIN rsrchdoc NATURAL JOIN lab;
 

в отделениях и лабораториях есть столбец кода больницы для сопоставления информации о работнике с конкретной больницей.
итак, у меня есть одна схема для всего персонала с их правилами workers(hospital_code, salary, type_of_worker)

вот запрос, который я пытаюсь создать:

     SELECT hospname, sum(workers.sal), avg(workers.sal), count(dept.numbed),
    (SELECT count(typework) from workers where typework = 'nurse') nurse_num,
    (SELECT count(typework) from workers where typework = 'rsrchdoc') rsrchdoc_num
    FROM hosp NATURAL JOIN dept NATURAL JOIN workers
    GROUP BY hospname;
 

Я хочу подсчитать для каждой больницы количество nurses и количество, research doctors
но это должно быть как-то соотнесено с разными hospitals (в приведенном выше это дает мне одинаковое количество медсестер / rsrchdocs для каждой больницы), должны быть столбцы, сгруппированные по именам госпиталей, и должны быть все кортежи, такие какинформация о зарплате (среднее значение, сумма), как я правильно понял, но информация о работниках должна быть сгруппирована HAVING typework = 'nurse' для nurse_num , а для столбца rsrchdoc_num она должна быть HAVING typework = 'rsrchdoc_num'

у кого-нибудь есть идея, как я могу объединить столбцы thouse в одном запросе?

Спасибо!

В вашем запросе есть ошибка, я постараюсь объяснить.

Когда вы это сделаете:

 (SELECT count(typework) from workers where typework = 'nurse') nurse_num,
 

Вы получаете константу, на которую не влияет «группировка по», которую вы делаете после.

Что вам нужно сделать, это ОБЪЕДИНИТЬ (как вы сделали в представлении) и связать медсестру и rsrchdoc с конкретной больницей. Я приведу пример псевдокода

 SELECT hosp_name, sum(nurse.salary) , avg(nurse.salary)
FROM hosp
JOIN nurse ON nurse.hosp_name = hosp.hosp_name
GROUP BY hosp.hosp_name
 

Этот запрос даст вам 1 строку для каждой медсестры в каждой больнице (при условии, что медсестра может работать более чем в одной больнице).

Затем вам нужно сделать то же самое и для врачей, в другой операции.

 SELECT hosp_name, sum(doctors.salary) , avg(doctors.salary)
FROM hosp
JOIN doctors ON doctors.hosp_name = hosp.hosp_name
GROUP BY hosp.hosp_name
 

И, наконец, вам придется объединить оба (вы можете сначала выполнить суммирование, чтобы сделать его более читаемым.

 SELECT hosp_name, sum_sal_doc, avg_sal_doc, sum_nur_doc, avg_nur_doc
FROM hosp
LEFT JOIN ( SELECT doctors.hosp_name, sum(doctors.salary) as sum_sal_doc, avg(doctors.salary) as avg_sal_doc
  FROM doctors
  GROUP BY doctors.hosp_name
) t1 ON t1.hosp_name = hosp.hosp_name
LEFT JOIN ( SELECT nurses.hosp_name, sum(nurses.salary) as sum_nur_doc, avg(nurses.salary) as avg_nur_doc
  FROM nurses
  GROUP BY nurses.hosp_name
) t2 ON t2.hosp_name = hosp.hosp_name
 

1 to many Между hosp --> dept и hosp --> workers поэтому, если вы присоединитесь к этим 3 таблицам, вы обязательно найдете дубликаты для dept и workers поэтому вам необходимо создать подзапрос для одного из dept или workers для извлечения одной сгруппированной записи group by больницы следующим образом:

 SELECT h.hospname, 
       sum(w.sal) total_all_worker_sal, 
       avg(w.sal) avg_all_workers_sal, 
       d.numbed,
       count(case when w.typework = 'nurse' then 1 end) nurse_num,
       count(case when w.typework = 'rsrchdoc' then 1 end) rsrchdoc_num
  FROM hosp h 
  JOIN (select hospital_code , sum(numbed) numbed 
        -- used SUM as numbed must be number of bed in department
        -- COUNT will give you only number of department if you use count(d.numbed)
          from dept 
        group by hospital_code) d ON h.hospital_code = d.hospital_code 
  JOIN workers w ON h.hospital_code = d.hospital_code
 GROUP BY h.hospital_code , h.hospname, d.numbed; 
 -- used h.hospital_code to separate the records if two hospitals have same name