编写SQL查询：展示员工姓名、已休天数、可休天数及休假占比

Let's work through this step by step. Your current query has a few key issues—like incorrect date calculations, a misspelled table name, and tangled subquery logic. Let's build a clean, correct query that returns the exact output you need.

First, Let's Address the Issues in Your Original Query #

• Date Calculation Error: STARTDATE-ENDDATE gives a negative number and doesn’t account for inclusive days (e.g., 1/1 to 1/11 is 11 days, not 10). Use ENDDATE - STARTDATE + 1 to get accurate leave day counts.
• Table Name Typo: JOBROLESELIGIBELE should be JOBROLESELIGIBLELE.
• Subquery Logic: You can’t compute aliases like TAKENLEAVE in a WHERE clause, and your subqueries don’t properly aggregate data per employee or join the tables together.
• Missing Aggregation: You need to sum leave days per employee instead of checking individual leave records one by one.

Correct SQL Query #

Here's the query that will return the expected columns:

SELECT
    e.EMPLOYEEID,
    e.FIRSTNAME,
    e.LASTNAME,
    NVL(l.LEAVESTAKEN, 0) AS LEAVESTAKEN,
    j.ELIGIBLE_SICK_LEAVES + j.ELIGIBLE_EARNED_LEAVES AS ELIGIBLELEAVES,
    ROUND((NVL(l.LEAVESTAKEN, 0) / (j.ELIGIBLE_SICK_LEAVES + j.ELIGIBLE_EARNED_LEAVES)) * 100, 2) AS PERCENTAGE
FROM
    Employee24 e
LEFT JOIN (
    -- Calculate total leave days per employee
    SELECT
        EMPLOYEEID,
        SUM(ENDDATE - STARTDATE + 1) AS LEAVESTAKEN
    FROM Leave25
    GROUP BY EMPLOYEEID
) l ON e.EMPLOYEEID = l.EMPLOYEEID
JOIN JOBROLESELIGIBLELE j ON e.JOBROLES = j.JOBROLES
ORDER BY e.EMPLOYEEID;

Breakdown of the Query #

1. Subquery for Aggregated Leave Days: The inner subquery groups Leave25 by employee ID and sums up total leave days using SUM(ENDDATE - STARTDATE + 1)—this counts both the start and end dates of each leave period.
2. Table Joins:
   • LEFT JOIN with the aggregated leave data ensures employees with no leave records still appear in the results (with 0 leave days).
   • JOIN with JOBROLESELIGIBLELE links each employee to their job role's leave eligibility.
3. Metric Calculations:
   • NVL(l.LEAVESTAKEN, 0) replaces NULL (for employees with no leave) with 0.
   • ELIGIBLELEAVES combines sick and earned leave eligibility for the employee's role.
   • PERCENTAGE calculates the ratio of taken leave to eligible leave, rounded to 2 decimal places for readability.

Expected Result (Matching Sample Data) #

(Note: FIRSTNAME and LASTNAME are NULL in the sample data because the Employee24 create statement only populates EMPLOYEEID and JOBROLES.)

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