Oracle SQL一对多关联查询：按目录和类型统计员工数量

Got it, let's tackle this problem step by step. To get the count of employees per directory and type, we need to properly join the four tables based on their relationships, then aggregate the results.

First, here's the SQL query that will give you exactly the output you're expecting:

SELECT 
    d.dir,
    e.type,
    COUNT(e.id) AS "# of emp"
FROM 
    directory d
JOIN 
    assigned_num an ON SUBSTR(an.no, 1, 4) = d.first_char
JOIN 
    emp e ON an.emp_id = e.id
GROUP BY 
    d.dir, e.type
ORDER BY 
    d.dir, e.type;

Breakdown of the query: #

• Table Joins:
  • We link directory to assigned_num by using SUBSTR(an.no, 1, 4) to grab the first 4 characters of the no field, which matches the first_char in the directory table. This maps each employee to their correct directory.
  • Next, we join assigned_num with emp via the emp_id field to pull in each employee's type.
• Aggregation: Grouping by dir and type lets us use COUNT(e.id) to tally up how many employees belong to each directory-type pair.
• Ordering: The ORDER BY clause sorts results to match the structure of your expected output, making it easy to cross-verify.

If you ever need to include all possible directory-type combinations (even those with 0 employees, like Dir0 + typeC), you can use a cross join with emp_type and left joins to retain every possible pair:

SELECT 
    d.dir,
    et.type,
    COUNT(e.id) AS "# of emp"
FROM 
    directory d
CROSS JOIN 
    emp_type et
LEFT JOIN 
    assigned_num an ON SUBSTR(an.no, 1, 4) = d.first_char
LEFT JOIN 
    emp e ON an.emp_id = e.id AND e.type = et.type
GROUP BY 
    d.dir, et.type
ORDER BY 
    d.dir, et.type;

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