Imagine you have a table of employee data that includes each employee's name, job title, and the name of their supervisor. You want to find the total number of employees in each job category.
WITH RECURSIVE job_categories AS (
SELECT job_title, COUNT(*) AS employee_count
FROM employee_data
GROUP BY job_title
UNION ALL
SELECT e.job_title, COUNT(*) AS employee_count
FROM employee_data e
JOIN job_categories jc ON e.supervisor = jc.job_title
GROUP BY e.job_title
)
SELECT job_title, SUM(employee_count) AS total_employees
FROM job_categories
GROUP BY job_title;I saw this code in a tutorial about RECURSIVE clause in SQL. In the JOIN section i don't understood how could it be the comparison between the supervisor name and the job title.. It does not contain the same data. Job title it can be like "developer" and supervisor name it can be something like "Yu Lee".
I will be happy if someone can explain me this QUERY and how it works 😁😁
Thanks for your time and efforts.
1条答案
按热度按时间fcipmucu1#
It's not at all clear what you're trying to achieve here, as you've provided no example source data or expected output.
In a simple case, where you have a table of Employees with the Job Titles:
You can simply
GROUP BYthejob_titlefield and do aCOUNT():Results:
If you're trying to do something more involved than this, then you need to better explain your database schema, provide some example data and expected output, as described here: