You need to enable JavaScript to run this app. MySQL多场景查询实现咨询:含单表多比较、多表关联四类需求
优惠活动
大模型
产品
解决方案
定价
更多

MySQL多场景查询实现咨询:含单表多比较、多表关联四类需求

MySQL多表查询需求实现方案

以下实现基于给出的office、staff两张表结构,兼容MySQL 8.0及以上版本,特殊场景兼容方案会单独标注。


a) 查询所有薪资高于其直属主管的员工

逻辑:对staff表做自连接,将员工记录与对应直属主管的记录关联,筛选员工薪资大于主管薪资的条目即可。

SELECT 
    s.id AS staff_id,
    s.name AS staff_name,
    s.wage AS staff_wage,
    m.name AS chief_name,
    m.wage AS chief_wage
FROM staff s
INNER JOIN staff m ON s.chief_id = m.id
WHERE s.wage > m.wage;

b) 查询所有办事处列表,以及各办事处内薪资最高的员工(无员工的办事处也输出,最高薪资多人并列则全部展示)

逻辑:先用窗口函数DENSE_RANK()对每个办事处的员工按薪资降序排名,取排名为1的即为当前办事处最高薪资员工;再通过office表左连接上述结果,保证无员工的办事处也会被输出。

WITH staff_rank AS (
    SELECT 
        *,
        DENSE_RANK() OVER(PARTITION BY office_id ORDER BY wage DESC) AS wage_rank
    FROM staff
)
SELECT 
    o.id AS office_id,
    o.name AS office_name,
    sr.id AS staff_id,
    sr.name AS staff_name,
    sr.wage AS max_wage
FROM office o
LEFT JOIN staff_rank sr ON o.id = sr.office_id AND sr.wage_rank = 1;

如果使用不支持CTE和窗口函数的MySQL 5.7版本,可以用子查询实现:

SELECT 
    o.id AS office_id,
    o.name AS office_name,
    s.id AS staff_id,
    s.name AS staff_name,
    s.wage AS max_wage
FROM office o
LEFT JOIN staff s ON o.id = s.office_id
LEFT JOIN (
    SELECT office_id, MAX(wage) AS max_wage
    FROM staff
    GROUP BY office_id
) t ON s.office_id = t.office_id AND s.wage = t.max_wage
WHERE s.id IS NULL OR s.wage = t.max_wage;

c) 查询所有仅有1名直属下属的主管

逻辑:先统计每个chief_id对应的下属数量,筛选出数量为1的chief_id,再关联staff表取出主管的详细信息即可。

SELECT 
    m.id AS chief_id,
    m.name AS chief_name,
    COUNT(s.id) AS subordinate_count
FROM staff m
INNER JOIN staff s ON m.id = s.chief_id
GROUP BY m.id, m.name
HAVING COUNT(s.id) = 1;

d) 查询所有办事处按其下属员工总薪资降序排序的结果

逻辑:office表左连接staff表,按办事处维度分组统计总薪资,无员工的办事处总薪资为0,最后按总薪资降序排序即可。

SELECT 
    o.id AS office_id,
    o.name AS office_name,
    IFNULL(SUM(s.wage), 0) AS total_wage
FROM office o
LEFT JOIN staff s ON o.id = s.office_id
GROUP BY o.id, o.name
ORDER BY total_wage DESC;

内容的提问来源于stack exchange,提问作者strangedenial

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.14 00:03:02