mysql查询结果合并去重、合并全部

最新推荐文章于 2025-05-29 17:59:40 发布

巨行心

最新推荐文章于 2025-05-29 17:59:40 发布

阅读量5.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/ssssny/article/details/52381746

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种通过SQL查询来统计分析医生在住院部、门诊及随访中的诊疗情况的方法。通过对医生基本信息表与各诊疗场景表进行左连接，并使用UNION ALL及GROUP BY对数据进行汇总，最终得到每位医生的诊疗次数排名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<span style="font-family: Arial, Helvetica, sans-serif;">SELECT doctor_id,doctor_name,doctor_position,COUNT(*) amount</span>

FROM
(
(SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN hospitalized h ON d.doctor_id=h.doctor_id)
UNION ALL
(SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN u_outpatient o ON o.doctor_id = d.doctor_id
)
UNION ALL
(
SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN med_follow f ON f.doctor_id = d.doctor_id
)
) dd
GROUP BY doctor_name
ORDER BY amount DESC

合并全部

======================================================

合并去重

(SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN hospitalized h ON d.doctor_id=h.doctor_id)
UNION
(SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN u_outpatient o ON o.doctor_id = d.doctor_id
)
UNION
(
SELECT d.doctor_id,d.doctor_name,d.doctor_position
FROM doctor_info d 
LEFT JOIN med_follow f ON f.doctor_id = d.doctor_id
)