求编写MySQL JOIN查询语句获取指定项目的开发者工时等信息

Hey there! Let's break this down step by step to get you the exact query you need. First, I'll assume your database uses these common table structures (adjust if your actual schema differs):

• projects: Stores project core data (project_id, project_name, lead_user_id → the user ID of the project's responsible lead)
• users: Holds user information (user_id, username → to identify individual developers)
• developer_worklogs: Tracks each developer's project contributions (worklog_id, project_id, developer_user_id, working_hours, overtime_hours, descriptions, contributions)

The JOIN Query #

Here's the query that meets your requirement, with comments to clarify each part:

SELECT
    u.username AS developer_name,
    dw.working_hours AS workingHours,
    dw.overtime_hours AS Overtime,
    dw.descriptions AS Descriptions,
    dw.contributions AS Contributions
FROM
    projects p
JOIN
    developer_worklogs dw ON p.project_id = dw.project_id
JOIN
    users u ON dw.developer_user_id = u.user_id
WHERE
    -- Restrict access to only the lead's own project
    p.lead_user_id = 123 -- Replace with the logged-in lead's user ID
    AND
    -- Target the specific project the lead wants to view
    p.project_id = 456; -- Replace with the target project ID

Key Details to Adjust for Your Schema: #

• Security: Always use parameterized queries (instead of hardcoding IDs) in your application to avoid SQL injection. For example, use ? placeholders in Java prepared statements or %s in Python's MySQL connectors.
• Table/Column Names: Swap out identifiers like developer_worklogs or working_hours to match your actual database schema.
• Including Inactive Developers: If you want to show developers assigned to the project but with no work logs yet, replace JOIN with LEFT JOIN between projects and developer_worklogs (you'll get NULL values for their work fields).

Example Output (Matching Your Requested Format): #

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