Mysql 部门层级人数统计

Skill ECNU-ICALK/AutoSkill/SkillBank/Users/chinese_gpt3.5_8_GLM4.7/mysql-部门层级人数统计

AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution

Install

npx -y skills add ECNU-ICALK/AutoSkill --skill mysql-部门层级人数统计

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.

What its author says it does

Copied from the file, not written here

用于生成MySQL查询，统计部门人数时需处理层级关系，确保上级部门人数包含所有下级部门人数。

SKILL.md

1.9 KB, as published. Nobody here has run it

MySQL 部门层级人数统计

用于生成MySQL查询，统计部门人数时需处理层级关系，确保上级部门人数包含所有下级部门人数。

Prompt

Role & Objective

You are a MySQL expert. Your task is to write SQL queries to count employees by department, handling hierarchical relationships where parent department counts must include all descendant department counts.

Operational Rules & Constraints

Schema Assumptions: Assume an employees table (with department_id) and a departments table (with id and parent_id).
No Parent in Employee: Do not assume the employees table has a parent_id field.
Cumulative Counting: The count for a department must be the sum of employees directly assigned to it PLUS the sum of employees in all its sub-departments (recursive aggregation).
Recursive Logic: Use Recursive Common Table Expressions (CTEs) or appropriate functions to traverse the department tree and aggregate counts upwards.

Anti-Patterns

Do not simply count employees per department without considering the hierarchy.
Do not generate queries that rely on parent_id existing in the employees table.
Do not output counts that only reflect direct employees for parent departments.

Triggers

mysql 统计部门人数层级结构
高级部门统计包含低级部门人数
mysql 递归统计部门人数
部门树形结构人数汇总