mysql语句，简明门诊管理系统数据高级查询

根据简明门诊管理系统业务需求，在对数据进行查询时还常常需要对数据进行汇总统计，如汇总医生总人数、计算病人平均年龄、统计不同科室的医生人数等。本任务主要学习简明门诊管理系统的数据高级查询功能，主要包括汇总数据、分组统计、多表连接查询、嵌套查询及联合查询等。

一.数据表

使用图形化工具或SQL语句在简明门诊管理系统数据库his中创建数据表

表2-3-9 department（科室信息表）

字段名称	数据类型	长度	是否为空	说明
dep_ID	int		否	科室编号（主键，自动递增）
dep_Name	varchar	50	是	科室名
dep_Address	varchar	200	是	地址

表2-3-10 doctor（医生信息表）

字段名称	数据类型	长度	是否为空	说明
d_ID	int		否	医生编号（主键，自动递增）
d_Name	varchar	50	是	姓名
d_Sex	char	1	是	性别（默认值为男）
d_Profession	varchar	50	是	职称
d_LoginName	varchar	50	是	登录名
d_LoginPSW	varchar	50	是	密码
dep_ID	int		是	科室编号

表2-3-11 patientstatus（就诊状态表）

字段名称	数据类型	长度	是否为空	说明
ps_ID	int		否	就诊状态编号（主键，自动递增）
ps_Name	varchar	20	是	状态名
ps_Remark	varchar	100	是	备注

表2-3-12 patient（病人信息表）

字段名称	数据类型	长度	是否为空	说明
p_ID	int		否	病人编号（主键，自动递增）
p_Name	varchar	50	是	姓名
p_Sex	char	1	是	性别(默认值为男)
p_Address	varchar	50	是	家庭地址
p_Birth	datetime		是	出生日期
ps_ID	int		是	就诊状态编号

表2-3-13 diagnosis（诊疗信息表）

字段名称	数据类型	长度	是否为空	说明
dia_ID	int		否	编号(主键，自动递增)
d_ID	int		是	医生编号
p_ID	int		是	病人编号
dia_Time	datetime		是	诊疗时间
dia_Symptom	varchar	1000	是	症状
dia_Diagnosis	varchar	1000	是	诊断
dia_Dispense	varchar	1000	是	配药
dia_Remark	varchar	1000	是	备注

表2-3-14 worker（挂号员信息表）

字段名称	数据类型	长度	是否为空	说明
w_ID	int		否	挂号员编号(主键，自动递增)
w_Name	varchar	20	是	姓名
w_LoginName	varchar	50	是	登录名
w_LoginPSW	varchar	50	是	密码

表2-3-15 register（挂号信息表）

字段名称	数据类型	长度	是否为空	说明
reg_ID	int		否	编号(主键，自动递增)
dep_ID	int		是	科室编号
p_ID	int		是	病人编号
w_ID	int		是	挂号员编号
reg_Time	datetime		是	挂号时间
reg_Fee	int		是	费用
reg_Order	int		是	次序
reg_Status	int		是	状态

二.题目及答案

1. 数据查询统计

（1）统计80后病人中男、女病人的平均年龄、最大年龄、最小年龄，结果按照平均年龄降序排列。

SELECTp_Sex,AVG(YEAR(CURDATE())-YEAR(p_Birth))AS 平均年龄, 
MAX(YEAR(CURDATE())-YEAR(p_Birth))AS 最大年龄,
 MIN(YEAR(CURDATE())-YEAR(p_Birth))AS 最小年龄 
FROMpatient 
WHEREYEAR(p_Birth)BETWEEN1980AND1989 
GROUPBYp_Sex ORDERBY 平均年龄 DESC

（2）统计各楼层科室数，结果按照科室数降序排列，结果包含地址、科室数。

mysql> select dep_Address,count(*) as 科室数

    -> from department

    -> oreder;

（3）统计编号为“1”的挂号员挂号的各科室的平均费用、最高费用、最低费用，结果包含科室编号、平均费用、最高费用、最低费用，并按平均费用降序排列。

mysql> select w_ID as 科室编号,avg(reg_Fee) as 平均费用,max(reg_Fee) as 最高费用,min(reg_Fee) as 最低费用

    -> from register

    -> where w_ID='1'

    -> group by dep_ID='1'

    -> order by 平均费用 desc;

（4）统计全院各科室的医生人数。

mysql> select dep_ID as 科室编号,count(d_ID) as 医生人数

    -> from doctor

    -> group by 科室编号;

（5）统计男、女病人人数。

mysql> select p_Sex,count(p_ID) as 病人人数

    -> from patient

    -> group by p_Sex;

（6）统计男主任医师人数大于1的科室的科室编号、主任医师人数，结果按人数降序排列。

mysql> select dep_ID,count(*) as 人数

    -> from doctor

    -> where d_Sex = '男' and d_Profession = '主任医师'

    -> group by dep_ID

    -> having count(*)>1

    -> order by 人数 desc;

（7）统计诊疗信息表中2011年11月诊断信息超过1条记录的医生的医生编号。

mysql> select d_ID

    -> from diagnosis

    -> where year(dia_Time) =2011 and month(dia_Time) =11

    -> group by d_ID

    -> having count(*)>1;

（8）统计各年份出生的男病人数，筛选出男病人数不小于2的年份，结果按人数降序排列。

mysql> select year(p_Birth), count(*) as 人数

    -> from patient

    -> where p_Sex = '男'

    -> group by year(p_Birth)

    -> having count(*)>=2

    -> order by 人数 desc;

（9）统计各姓氏的女病人数，筛选出女病人数不小于2的姓氏，结果按人数降序排列。

mysql> select left(p_Name,1) as 姓氏, count(p_Name) as 女病人数

    -> from patient

    -> where p_Sex = '女'

    -> group by 姓氏

    -> having count(人数)>=2

    -> order by 女病人数 desc;

2. 连接查询

（1）查询在门诊楼一楼工作的主任医师的信息。

mysql> select doctor.*

    -> from department

    -> inner join doctor on department.dep_ID = doctor.dep_ID

    -> where dep_Address = '门诊楼一楼' and d_Profession = '主任医师';

（2）查询吕正太医生的诊疗记录。

SELECT diagnosis.*
FROM doctor JOIN diagnosis ON doctor.d_ID=diagnosis.d_ID 
WHERE d_Name='吕正太'
或 
SELECT diagnosis.* 
from doctor,diagnosis 
where doctor.d_ID=diagnosis.d_ID and d_Name='吕正太' 

3. 嵌套查询

（1）查询在门诊楼一楼上班的医生的信息。

mysql> SELECT doctor.*
    -> FROM doctor
    -> INNER JOIN department ON doctor.dep_ID = department.dep_ID
    -> WHERE department.dep_Address = '门诊楼一楼';
或
SELECT* 
FROM doctor 
WHERE dep_ID IN 
(
SELECT dep_ID 
FROM department 
WHERE dep_Address='门诊楼一楼'
)

（2）查询就诊状态为“挂号完毕，待诊”的病人的信息。

mysql> SELECT patient.*
    -> FROM patient
    -> INNER JOIN patientstatus ON patient.ps_ID = patientstatus.ps_ID
    -> WHERE patientstatus.ps_Name = '挂号完毕，待诊';
或
SELECT* 
FROM patient 
WHERE ps_ID = 
(
SELECT ps_ID 
FROM patientstatus 
WHERE ps_Name='挂号完毕，待诊'
)

（3）查询比所有女病人年龄都大的男病人的信息。

mysql> SELECT MAX(p_Birth) AS max_female_birth
    -> FROM patient
    -> WHERE p_Sex = '女';

+---------------------+
| max_female_birth    |
+---------------------+
| 2001-04-02 00:00:00 |
+---------------------+
1 row in set (0.05 sec)

mysql> SELECT patient.*
    -> FROM patient
    -> WHERE p_Sex = '男'
    -> AND p_Birth < (SELECT MAX(p_Birth) AS max_female_birth
    ->  FROM patient
    ->  WHERE p_Sex = '女');

 或参考

SELECT*
FROM Patient
WHERE p_Sex='男'AND YEAR(CURDATE())-YEAR(p_Birth)>
(
SELECT MAX(YEAR(CURDATE())-YEAR(p_Birth))
FROM patient
WHERE p_Sex='女'
)
或
SELECT*
FROM Patient
WHERE p_Sex='男'AND YEAR(CURDATE())-YEAR(p_Birth)>ALL
(
SELECT YEAR(CURDATE())-YEAR(p_Birth)
FROM patient
WHERE p_Sex='女'
)

4. 联合查询

（1）查询各科室的总挂号费用及所有科室的总挂号费用。

mysql> SELECT d.dep_Name, SUM(r.reg_Fee) AS total_registration_fee
    -> FROM department d
    -> LEFT JOIN register r ON d.dep_ID = r.dep_ID
    -> GROUP BY d.dep_ID, d.dep_Name;
或
SELECT dep_Name AS 科室名 ,SUM(reg_Fee)AS 总挂号费用
FROM register a JOIN department b ON a.dep_ID = b.dep_ID 
GROUP BY dep_Name 
UNION 
SELECT'所有科室',SUM(reg_Fee)AS 总挂号费用 
FROM register

（2）查询男、女病人的平均年龄及全部病人的平均年龄。

查询男病人的平均年龄

SELECT AVG(TIMESTAMPDIFF(YEAR, p_Birth, CURRENT_DATE())) AS average_age_male
FROM patient
WHERE p_Sex = '男';

在上述语句中：

• TIMESTAMPDIFF(YEAR, p_Birth, CURRENT_DATE()) 这个函数的作用是计算从病人的出生日期 p_Birth 到当前日期 CURRENT_DATE() 之间相差的年数，也就是大致估算出病人的年龄。其中 YEAR 表示按照年为单位来计算时间差。
• 通过 WHERE p_Sex = '男' 条件筛选出男病人的记录，然后使用 AVG() 函数对这些男病人的年龄进行求平均操作，并使用 AS 关键字将结果命名为 average_age_male，用于表示男病人的平均年龄。

查询女病人的平均年龄

SELECT AVG(TIMESTAMPDIFF(YEAR, p_Birth, CURRENT_DATE())) AS average_age_female
FROM patient
WHERE p_Sex = '女';

此语句与查询男病人平均年龄的语句逻辑类似，只是将筛选条件改为 WHERE p_Sex = '女'，以此来筛选出女病人的记录，进而计算并得到女病人的平均年龄，将结果命名为 average_age_female。

查询全部病人的平均年龄

SELECT AVG(TIMESTAMPDIFF(YEAR, p_Birth, CURRENT_DATE())) AS average_age_all
FROM patient;

 或参考

SELECT p_Sex AS 性别 ,AVG(YEAR(CURDATE())-YEAR(p_Birth))AS 平
均年龄
FROM patient
GROUP BY p_Sex
UNION
SELECT '全部病人' ,AVG(YEAR(CURDATE())-YEAR(p_Birth))AS 平均年
龄
FROM patient