MySQL跨表统计多列数据：基于员工主表与打卡表的统计需求

Got it, let's break down how to get the stats you need from the employee_master and punching_data_table tables. I'll share two common approaches depending on your desired output format.

Option 1: Row per Sub-Department (with Department Total) #

This query returns each sub-department as a separate row, along with the total number of employees in its parent department. It only includes employees who have a punch record—swap INNER JOIN with LEFT JOIN if you need to include employees without punches.

SELECT
    em.dept_id,
    -- Calculate total employees in the department using a window function
    COUNT(DISTINCT em.emp_code) OVER (PARTITION BY em.dept_id) AS dept_count,
    em.sub_dept_id,
    -- Count employees in the specific sub-department
    COUNT(DISTINCT em.emp_code) AS sub_dept_count
FROM
    employee_master em
INNER JOIN
    punching_data_table pd ON em.emp_code = pd.emp_code
GROUP BY
    em.dept_id, em.sub_dept_id
ORDER BY
    em.dept_id, em.sub_dept_id;

Sample Output: #

Option 2: Row per Department (with Aggregated Sub-Department Counts) #

If you prefer a single row per department with all sub-department stats in one field, use GROUP_CONCAT to aggregate the sub-department counts into a readable string:

SELECT
    em.dept_id,
    COUNT(DISTINCT em.emp_code) AS dept_count,
    -- Aggregate sub-department counts into a single formatted string
    GROUP_CONCAT(
        CONCAT('sub_dept_', em.sub_dept_id, ': ', COUNT(DISTINCT em.emp_code))
        SEPARATOR ', '
    ) AS sub_dept_counts
FROM
    employee_master em
INNER JOIN
    punching_data_table pd ON em.emp_code = pd.emp_code
GROUP BY
    em.dept_id
ORDER BY
    em.dept_id;

Sample Output: #

Key Notes #

• Use DISTINCT in COUNT() to avoid duplicate counts if an employee has multiple punch records (your sample data has one per employee, but it's a safe guard for real-world scenarios).
• Switch to LEFT JOIN instead of INNER JOIN if you need to include employees who didn't punch in—their present value will be NULL, but they'll still be counted in the department/sub-department totals.

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