如何编写SQL查询按当前薪资周期内员工工时降序排序展示

你当前使用的查询仅统计了所有员工的总工时，要获取工时、薪资的极值员工数据，可以参考以下实现：

单条统计查询（按需调用） #

• 工时最高员工

SELECT employee_id, employee_name, SUM(totalTime) AS total_work_time
FROM payroll_details 
WHERE payroll_id = ?
GROUP BY employee_id, employee_name
ORDER BY total_work_time DESC
LIMIT 1;

• 工时最低员工

SELECT employee_id, employee_name, SUM(totalTime) AS total_work_time
FROM payroll_details 
WHERE payroll_id = ?
GROUP BY employee_id, employee_name
ORDER BY total_work_time ASC
LIMIT 1;

• 薪资最高员工（请将salary替换为你表中实际的薪资字段名）

SELECT employee_id, employee_name, salary
FROM payroll_details 
WHERE payroll_id = ?
GROUP BY employee_id, employee_name
ORDER BY salary DESC
LIMIT 1;

• 薪资最低员工（请将salary替换为你表中实际的薪资字段名）

SELECT employee_id, employee_name, salary
FROM payroll_details 
WHERE payroll_id = ?
GROUP BY employee_id, employee_name
ORDER BY salary ASC
LIMIT 1;

一次性返回所有统计数据 #

如果希望一次查询拿到全部所需数据，减少数据库交互，可使用CTE合并查询：

WITH employee_aggr AS (
    SELECT 
        employee_id,
        employee_name,
        SUM(totalTime) AS total_work_time,
        SUM(salary) AS total_salary -- 薪资按你的统计逻辑换聚合函数，比如MAX、AVG
    FROM payroll_details
    WHERE payroll_id = ?
    GROUP BY employee_id, employee_name
)
SELECT
    -- 总工时最高员工
    (SELECT employee_name FROM employee_aggr ORDER BY total_work_time DESC LIMIT 1) AS max_time_emp_name,
    (SELECT total_work_time FROM employee_aggr ORDER BY total_work_time DESC LIMIT 1) AS max_time_value,
    -- 总工时最低员工
    (SELECT employee_name FROM employee_aggr ORDER BY total_work_time ASC LIMIT 1) AS min_time_emp_name,
    (SELECT total_work_time FROM employee_aggr ORDER BY total_work_time ASC LIMIT 1) AS min_time_value,
    -- 薪资最高员工
    (SELECT employee_name FROM employee_aggr ORDER BY total_salary DESC LIMIT 1) AS max_salary_emp_name,
    (SELECT total_salary FROM employee_aggr ORDER BY total_salary DESC LIMIT 1) AS max_salary_value,
    -- 薪资最低员工
    (SELECT employee_name FROM employee_aggr ORDER BY total_salary ASC LIMIT 1) AS min_salary_emp_name,
    (SELECT total_salary FROM employee_aggr ORDER BY total_salary ASC LIMIT 1) AS min_salary_value,
    -- 原有总工时统计
    SUM(total_work_time) AS all_emp_total_time
FROM employee_aggr;

注意：如果你的payroll_details表中同一个员工对应多条薪资周期记录，薪资的聚合逻辑请按实际业务调整，按周期发薪用SUM、月薪固定用MAX/MIN都可以，替换对应位置的聚合函数即可。

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