mysql - 显示超支项目数量的最佳方式

Question

我将第一个 View 显示为:

CREATE VIEW Projects AS
    SELECT DepartmentName, 
        COUNT(*)NumberOfProjects,
        OfficeNumber,
        Phone
    FROM   DEPARTMENT AS D JOIN PROJECT AS P
            ON D.DepartmentName = P.Department

    GROUP BY Department;

但是，我想在此 View 中为每个部门显示“NumberOfOverBudgetProjects”。我创建了一个 View ，其中列出了哪些项目超出了他们的小时预算:

CREATE VIEW Budgets AS
    SELECT Department, 
        MaxHours AS ProjectMaxHours, SUM(HoursWorked) AS TotalHoursWorked,
        SUM(HoursWorked) - MaxHours AS Balance
    FROM   PROJECT AS P JOIN ASSIGNMENT AS A
        ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID;

市场部应列出 2 个超出预算的项目，会计和财务部应列出 0 个项目。

关于如何在原始 View 中加入“OverBudgetProjects”列的任何想法？还是通过创建新 View ？

Answer 1

你可以这样做

CREATE VIEW OverBudgetProjects AS
  SELECT p.department, p.projectid
    FROM project p LEFT JOIN assignment a
      ON p.projectid = a.projectid
   GROUP BY p.department, p.projectid
  HAVING MAX(p.maxhours) < SUM(a.hoursworked);

CREATE VIEW Projects AS
  SELECT DepartmentName, 
         COUNT(DISTINCT p.projectid) NumberOfProjects,
         COUNT(DISTINCT o.Projectid) NumberOfOverBudgetProjects,
         OfficeNumber,
         Phone
    FROM department d JOIN project p
      ON d.DepartmentName = p.Department LEFT JOIN OverBudgetProjects o
      ON d.DepartmentName = o.Department
   GROUP BY p.Department;

发布的示例输出

SELECT * FROM Projects

是

| DEPARTMENTNAME | NUMBEROFPROJECTS | NUMBEROFOVERBUDGETPROJECTS | OFFICENUMBER |        PHONE ||----------------|------------------|----------------------------|--------------|--------------||     Accounting |                1 |                          0 |   BLDG01-100 | 360-285-8300 ||        Finance |                2 |                          0 |   BLDG01-140 | 360-285-8400 ||      Marketing |                2 |                          2 |   BLDG02-200 | 360-287-8700 |

这是 SQLFiddle 演示