代码之家 › 专栏 › 技术社区 › TheShield

合并两个表,新列旧列之和

db2 count select sql

TheShield · 技术社区 · 7 年前

SELECT COUNT(N.NurseID) AS Nurses, D.DName, D.HName
FROM Department AS D, Nurse_Work AS N
WHERE N.DName = D.DName AND N.HName = D.HName
GROUP BY D.DName, D.HName;

SELECT COUNT(P.PhysicianID) AS Physicians, D.DName, D.HName
FROM Physician AS P, Department AS D
WHERE P.DName = D.DName AND P.HName = D.HName
GROUP BY D.DName, D.HName;

SELECT COUNT(N.NurseID) AS Nurses, D.DName, D.HName FROM Department AS D, 
Nurse_Work AS N WHERE N.DName = D.DName AND N.HName = D.HName GROUP BY 
D.DName, D.HName

NURSES      DNAME                                                        HNAME                                                       
----------- -------------------------------- ----------------------------------
      8     General Surgery                                              H1                                  
     10     General Surgery                                              H3
      7     General Surgery                                              H4
     10     General Surgery                                              H5
      7     General Surgery                                              H2                          
      3     Intensive Care Unit                                          H1                                   
      2     Intensive Care Unit                                          H3
      1     Intensive Care Unit                                          H4
      2     Intensive Care Unit                                          H5
      3     Intensive Care Unit                                          H2
      6     Ophthalmology                                                H1                 
      4     Ophthalmology                                                H3
      4     Ophthalmology                                                H4
      2     Ophthalmology                                                H5
      2     Ophthalmology                                                H2
      3     Otolaryngology                                               H1    
      1     Otolaryngology                                               H3
      2     Otolaryngology                                               H4
      2     Otolaryngology                                               H5
      2     Otolaryngology                                               H2
      4     Radiology                                                    H1
      6     Radiology                                                    H3
      4     Radiology                                                    H4
      5     Radiology                                                    H5
      6     Radiology                                                    H2

25 record(s) selected.


SELECT COUNT(P.PhysicianID) AS Physicians, D.DName, D.HName FROM Physician AS 
P, Department AS D WHERE P.DName = D.DName AND P.HName = D.HName GROUP BY 
D.DName, D.HName

PHYSICIANS  DNAME                                                        HNAME                                                       
----------- ------------------------------------------------------------ ------------------------------------------------------------
      2 General Surgery                                              H1                              
      2 General Surgery                                              H3
      2 General Surgery                                              H4
      2 General Surgery                                              H5
      2 General Surgery                                              H2
      1 Intensive Care Unit                                          H1                                
      1 Intensive Care Unit                                          H3
      1 Intensive Care Unit                                          H4
      1 Intensive Care Unit                                          H5
      1 Intensive Care Unit                                          H2
      1 Ophthalmology                                                H1                                 
      1 Ophthalmology                                                H3
      1 Ophthalmology                                                H4
      1 Ophthalmology                                                H5
      1 Ophthalmology                                                H2
      1 Otolaryngology                                               H1                           
      1 Otolaryngology                                               H3
      1 Otolaryngology                                               H4
      1 Otolaryngology                                               H5
      1 Otolaryngology                                               H2
      1 Radiology                                                    H1                           
      1 Radiology                                                    H3
      1 Radiology                                                    H4
      1 Radiology                                                    H5
      1 Radiology                                                    H2

25 record(s) selected.

我的目标是找到员工(护士和医生)最多的科室及其医院,我已设法按职务分别计算了有多少员工,现在我想将结果表合并到一个表中,左侧的列是相应科室/医院组合的护士和医生的总和。我尝试使用JOIN和UNION,但是它们似乎只给我一个包含三列的结果表,其中一列同时显示医生和护士,值是分开的,而不是相加的。我怎样才能创建一个包含列的表,该列将汇总相应科室/医院组合的护士和医生的数量?

2 回复 | 直到 7 年前

Mureinik 7 年前

我会用两个聚合查询(每个表上一个)连接一个关于部门详细信息的查询,然后添加两个计数:

SELECT    d.dname, 
          d.hname,
          COALESCE(num_nurses, 0),
          COALESCE(num_physicians, 0),
          COALESCE(num_nurses, 0) + COALESCE(num_physicians, 0) AS total
FROM      department d
LEFT JOIN (SELECT   dname, hname, COUNT(*) AS num_nurses
           FROM     nurse_work
           GROUP BY dname, hname) n ON n.dname = d.dname AND n.hname = d.hname
LEFT JOIN (SELECT   dname, hname, COUNT(*) AS num_physicians
           FROM     physician
           GROUP BY dname, hname) p ON p.dname = d.dname AND p.hname = d.hname

Ajan Balakumaran 7 年前

我使用了你的基本查询,并使用UNION ALL将其组合在一起,因为UNION可能会删除重复项如果医院中护士和医生的计数相同,那么我将取医生和护士的总和。

Select DNAME,HName,SUM(CountAll) from
(SELECT COUNT(N.NurseID) AS CountAll, D.DName, D.HName
FROM Department AS D, Nurse_Work AS N
WHERE N.DName = D.DName AND N.HName = D.HName
GROUP BY D.DName, D.HName
UNION ALL
SELECT COUNT(P.PhysicianID) AS CountAll, D.DName, D.HName
FROM Physician AS P, Department AS D
WHERE P.DName = D.DName AND P.HName = D.HName
GROUP BY D.DName, D.HName)a
GROUP BY DNAME,HName