如何在sql中将不同元组中的不同值计数到同一场景中

Question

我有一个表，其中包括医院详细信息、链接到它的部门详细信息、不同表中的员工类型(员工)以及他们的工资信息。

我想提取每家医院的以下内容:工资的平均值和总和，护士的数量，研究医生的数量，以及特定医院所有部门的床位数量。

我构建了所有工人工资信息的视图：

    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 )，应该有按医院名称分组的列，并且应该获得所有的元组，如我正确获得的工资信息(avg，research doctors)，但是对于nurse_num，工人信息应该分组为HAVING typework = 'nurse'，对于列rsrchdoc_num，它应该是HAVING typework = 'rsrchdoc_num'

有没有人知道如何在一个查询中组合thouse列？

谢谢!

Answer 1

您的查询有误，我试着解释一下。

当你这样做的时候：

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

你得到一个常量，这个常量不受你之后执行的“ by”的影响。

您需要做的是连接(就像您在视图中所做的那样)，并将护士和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

此查询将为每个医院中的每个护士提供一行(假设一名护士可能在多家医院工作)。

然后你也必须为医生做同样的事情，在不同的手术中。

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

最后，您必须将两者连接起来(您可以先执行sum，以使其更具可读性。

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

Answer 2

hosp --> dept和hosp --> workers之间必须存在1 to many关系，因此如果您连接这3个表，则肯定会找到dept和workers的重复项，因此您必须为dept或workers之一创建子查询才能获取单个分组记录group by have，如下所示：

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