匹配员工与项目表，计算各项目月度费用乘积总和的技术问询

Got it, let's work through this problem to generate the project cost list you need. The core task is to link the employee cost data (salary and insurance) with their project hours, then compute the total monthly cost for each project.

Step 1: Reshape the Employees Table #

First, the Employees table stores salary and insurance as separate rows for each employee. We need to pivot these into columns so we can access both cost types for a given employee and month in one row. We'll use conditional aggregation to do this:

SELECT
    Year,
    Name,
    MAX(CASE WHEN Type = 'Salary' THEN Jan END) AS Jan_Salary,
    MAX(CASE WHEN Type = 'Insurance' THEN Jan END) AS Jan_Insurance,
    MAX(CASE WHEN Type = 'Salary' THEN Feb END) AS Feb_Salary,
    MAX(CASE WHEN Type = 'Insurance' THEN Feb END) AS Feb_Insurance
FROM Employees
GROUP BY Year, Name

This query gives us a clean view where each row has an employee's total monthly salary and insurance costs.

Step 2: Join with Projects and Compute Costs #

Next, we join this reshaped employee data with the Projects table, then calculate the total cost per project per month. The cost for each employee-project-month combination is the sum of their salary and insurance for that month multiplied by the hours they worked on the project. We then aggregate these values by project and year:

SELECT
    p.Year,
    p.Project_Name,
    SUM((e.Jan_Salary + e.Jan_Insurance) * p.Jan_Hours_Worked) AS Jan_Cost,
    SUM((e.Feb_Salary + e.Feb_Insurance) * p.Feb_Hours_Worked) AS Feb_Cost
FROM Projects p
JOIN (
    -- The reshaped employees subquery from Step 1
    SELECT
        Year,
        Name,
        MAX(CASE WHEN Type = 'Salary' THEN Jan END) AS Jan_Salary,
        MAX(CASE WHEN Type = 'Insurance' THEN Jan END) AS Jan_Insurance,
        MAX(CASE WHEN Type = 'Salary' THEN Feb END) AS Feb_Salary,
        MAX(CASE WHEN Type = 'Insurance' THEN Feb END) AS Feb_Insurance
    FROM Employees
    GROUP BY Year, Name
) e ON p.Year = e.Year AND p.Employee_Name = e.Name
GROUP BY p.Year, p.Project_Name

Result Explanation #

When you run this query, you'll get the following project cost breakdown:

Let's verify the numbers to make sense of it:

• For Apple in January: Kevin's total cost (5000+200) * 5 hours = 26000, plus Alex's total cost (3000+300)*7 hours =23100, totaling 49100.
• For LG in February: Kevin's total cost (2000+400)*3 hours=7200, and no other employees worked on LG that month.

内容的提问来源于stack exchange，提问作者Kev