SQL Server中现有表结构能否实现项目开发者工时关联查询？如何实现？

Hey there! Let's break down your question step by step—first, yes, you absolutely can pull that data with your table structure (assuming it has the core relationships we need), and I'll walk you through how to do it in SQL Server, plus any table tweaks if needed.

First: Do You Need to Adjust Your Table Structure? #

For this query to work, your schema needs three core components (if you already have these, no changes needed):

• A Projects table that links each project to its manager (e.g., has a ManagerID foreign key pointing to your employee table)
• An Employees table that lets you identify developers (either a Role field like 'Developer', or a link to a Roles lookup table)
• A time-tracking table (let's call it WorkHours for this example) that connects employees to projects, with WorkingHours and Overtime fields

If any of these pieces are missing (e.g., no ManagerID on projects, no way to filter for developers), you'll need to add those fields/relationships. Otherwise, you're good to go.

SQL Server Query Implementation #

Let's assume your table structure looks like this (swap names/fields to match your actual schema):

• Projects: ProjectID (PK), ProjectName, ManagerID (FK to Employees.EmployeeID)
• Employees: EmployeeID (PK), EmployeeName, Role (e.g., 'Developer', 'Manager')
• WorkHours: WorkHourID (PK), EmployeeID (FK), ProjectID (FK), WorkingHours, Overtime

Basic Query (Single Project, Current Manager) #

This query pulls the developer list, their regular hours, and overtime for the specific project you're responsible for:

SELECT
    e.EmployeeID,
    e.EmployeeName,
    wh.WorkingHours,
    wh.Overtime
FROM
    Projects p
-- Link the project to its manager to ensure you only access your projects
JOIN Employees manager ON p.ManagerID = manager.EmployeeID
-- Link to the time records for the project
JOIN WorkHours wh ON p.ProjectID = wh.ProjectID
-- Link to the developer details
JOIN Employees e ON wh.EmployeeID = e.EmployeeID
WHERE
    -- Replace with your target project ID, or use a parameter in an app
    p.ProjectID = 123
    -- Filter only for developers
    AND e.Role = 'Developer'
    -- Ensure you're only seeing projects YOU manage
    AND manager.EmployeeID = @CurrentUserID;

Aggregated Hours (e.g., Weekly/Monthly Totals) #

If you need to sum hours over a period (instead of individual entries), adjust with aggregate functions:

SELECT
    e.EmployeeID,
    e.EmployeeName,
    SUM(wh.WorkingHours) AS TotalWorkingHours,
    SUM(wh.Overtime) AS TotalOvertime,
    -- Group by week (adjust to MONTH, QUARTER, etc. as needed)
    DATEPART(WEEK, wh.RecordDate) AS WeekOfYear
FROM
    Projects p
JOIN Employees manager ON p.ManagerID = manager.EmployeeID
JOIN WorkHours wh ON p.ProjectID = wh.ProjectID
JOIN Employees e ON wh.EmployeeID = e.EmployeeID
WHERE
    p.ProjectID = 123
    AND e.Role = 'Developer'
    AND manager.EmployeeID = @CurrentUserID
    -- Filter by date range if needed
    AND wh.RecordDate BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    e.EmployeeID, e.EmployeeName, DATEPART(WEEK, wh.RecordDate);

Quick Table Tweaks If Needed #

If your schema is missing key pieces:

• No manager-project link: Add a ManagerID foreign key to Projects that references Employees.EmployeeID
• No way to identify developers: Add a Role field to Employees (varchar) or create a Roles lookup table with RoleID and RoleName, then add RoleID to Employees
• No project-employee time link: Ensure your time-tracking table has both ProjectID and EmployeeID foreign keys to connect the dots

Once those are in place, the queries above will work perfectly in SQL Server.

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