实验二：数据查询实验

【实验目的】

熟练运用SQL语言实现数据查询，包括单表查询、分组查询、连接查询、嵌套查询、集合查询、oralce数据库常用函数等

【实验内容】

根据“数据导入”文档中的语句，将“费用明细表”和“科室字典”2张excel表内容导入数据库。然后拟定以下内容的sql语句，根据实验报告模板完成实验报告。实验报告中（）的内容是需要自己填写的内容。各个内容前的知识点，只是参考内容，实现功能的sql有多种方式，只要满足内容的sql都可以

建表：

--创建费用明细表
CREATE TABLE 费用明细表
(ID号	VARCHAR2(20),		
住院次数	NUMBER(2),		
项目序号	NUMBER(6),		
项目代码	VARCHAR2(20),	
项目名称	VARCHAR2(120),	
开单科室代码	VARCHAR2(8),		
执行科室代码	VARCHAR2(8),		
数量	NUMBER(6,2),		
总金额	NUMBER(10,4),
计价时间	DATE
);
--导入数据
SELECT * FROM 费用明细表 FOR UPDATE;

--创建科室字典
CREATE TABLE 科室字典
(科室代码	VARCHAR2(8),		
 科室名称	VARCHAR2(50)
);
--导入数据
SELECT * FROM 科室字典 FOR UPDATE;

1、查询各项费用的开单科室名称和执行科室名称。查询结果为ID号、住院次数、项目序号、项目代码、项目名称、开单科室代码、开单科室名称、执行科室代码、执行科室名称、数量、总金额、计价时间

个人答案：

SELECT a.id号,
a.住院次数,
a.项目序号,
a.项目名称,
a.开单科室代码,
b.科室名称 开单科室名称,
a.执行科室代码,
c.科室名称 执行科室名称,
a.数量,
a.总金额,
a.计价时间
FROM 费用明细表 a,
     科室字典 b,
     科室字典 c
WHERE a.开单科室代码 = b.科室代码
AND a.执行科室代码 = c.科室代码

 参考答案：

SELECT a.id号,
       a.住院次数,
       a.项目序号,
       a.项目代码,
       a.项目名称,
       a.开单科室代码,
       b.科室名称 开单科室名称,
       a.执行科室代码,
       b.科室名称 执行科室名称,
       a.数量,
       a.总金额,
       a.计价时间
FROM 费用明细表 a,
     科室字典 b,
     科室字典 c
WHERE a.开单科室代码 = b.科室代码
AND   a.执行科室代码 = c.科室代码;

2、查询单价大于等于10并且小于等于100，计价时间在2点-4点之间（包括2点和4点）的项目代码、项目名称、单价（保留2为小数）、数量、总金额、计价时间。查询结果按先项目代码升序排序，再按按计价时间降序排序

个人答案：

select a.项目代码,
a.项目名称,
a.数量,
a.总金额,
a.计价时间,
 Round(a.总金额/a.数量,2) 单价，
a.总金额/a.数量
From 费用明细表 a
where a.总金额/a.数量 >=10 
and a.总金额/a.数量 <=100
and to_char(a.计价时间,'hh24') >= '02'
and to_char(a.计价时间,'hh24') <= '04'
order by a.项目代码,a.计价时间 desc

 参考答案：

SELECT a.项目代码,
       a.项目名称,
       round(a.总金额/a.数量,2) 单价,
       a.数量,
       a.总金额,
       a.计价时间
FROM 费用明细表 a
WHERE (a.总金额/a.数量) >= 10
AND   (a.总金额/a.数量) <= 100
AND   to_char(a.计价时间,'hh24') >= '02'
AND   to_char(a.计价时间,'hh24') <= '04'
ORDER BY a.项目代码,a.计价时间 DESC;

3、统计开单科室名称中包含“医生站”，开单科室收费总金额大于2000的开单科室和汇总金额信息。查询结果为开单科室代码、开单科室名称、开单科室总金额

个人答案：

select
a.开单科室代码,
b.科室名称 开单科室名称,
sum(a.总金额) 开单科室总金额
From 费用明细表 a,
     科室字典 b
where a.开单科室代码=b.科室代码
and b.科室名称 like'%医生站%'
group by  a.开单科室代码,
b.科室名称
having sum(a.总金额) > 2000;

 参考答案：

SELECT a.开单科室代码,
       b.科室名称 开单科室名称,
       SUM(a.总金额) 开单科室总金额
FROM 费用明细表 a,
     科室字典 b
WHERE a.开单科室代码 = b.科室代码
AND   b.科室名称 LIKE '%医生站%'
GROUP BY a.开单科室代码,
         b.科室名称
HAVING SUM(a.总金额) > 2000;

4、统计开单科室和收费总金额，将科室字典中的所有科室都显示出来。如果科室没有进行开单收费，则收费总金额留空。查询结果为开单科室代码、开单科室名称、开单科室总金额

个人答案：

select 
b.科室代码 开单科室代码,
b.科室名称 开单科室名称,
a.开单科室总金额
From 科室字典 b
left join (
select 开单科室代码，
sum（总金额） 开单科室总金额
from 费用明细表
group by 开单科室代码
) a on b.科室代码 = a.开单科室代码;

 参考答案：

SELECT a.科室代码, 
       a.科室名称,
       b.开单科室总金额
FROM 科室字典 a
LEFT JOIN (SELECT 开单科室代码,SUM(总金额) 开单科室总金额 FROM 费用明细表 GROUP BY 开单科室代码) b ON a.科室代码 = b.开单科室代码;

5、查询同一次住院（相同的id号和住院次数表示同一次住院），费用中既有'氯化钠注射液（输液）'又有'5%葡萄糖注射液'的患者，在这次住院期间费用名称为'氯化钠注射液（输液）'或者'5%葡萄糖注射液'的费用信息。查询结果显示费用明细表所有属性

个人答案：

select *
from 费用明细表 a
where not exists
(select * from 费用明细表 b
where a.ID号 = b.ID号
and a.住院次数 = b.住院次数
and a.项目名称 = '氯化钠注射液'
and not exists
(select *from 费用明细表 c
where b.ID号 = c.ID号
and b.住院次数 = b.住院次数
and c.项目名称 = '5%葡萄糖注射液'))
and not exists
(select * from 费用明细表 b
where a.ID号= b.ID号
and a.住院次数 = b.住院次数
and a.项目名称 = '5%葡萄糖注射液'
and not exists
(select * from 费用明细表 c
where b.ID号 = c.ID号
and b.住院次数 = b.住院次数
and c.项目名称 = '氯化钠注射液'))
and a.项目名称 in ('氯化钠注射液','5%葡萄糖注射液');

 参考答案（共三种）：

①

SELECT *
FROM 费用明细表 a
WHERE NOT EXISTS
      (SELECT * FROM 费用明细表 b
       WHERE a.id号 = b.id号
       AND   a.住院次数 = b.住院次数
       AND   a.项目名称 = '氯化钠注射液（输液）'
       AND   NOT EXISTS
             (SELECT * FROM 费用明细表 c
             WHERE b.id号 = c.id号
             AND   b.住院次数 = b.住院次数
             AND   c.项目名称 = '5%葡萄糖注射液'))
AND NOT EXISTS
      (SELECT * FROM 费用明细表 b
       WHERE a.id号 = b.id号
       AND   a.住院次数 = b.住院次数
       AND   a.项目名称 = '5%葡萄糖注射液'
       AND   NOT EXISTS
             (SELECT * FROM 费用明细表 c
             WHERE b.id号 = c.id号
             AND   b.住院次数 = b.住院次数
             AND   c.项目名称 = '氯化钠注射液（输液）'))
AND a.项目名称 IN ('氯化钠注射液（输液）','5%葡萄糖注射液');

②

SELECT *
FROM 费用明细表 a 
WHERE EXISTS
      (SELECT * FROM 费用明细表 b
       WHERE a.id号 = b.id号
       AND   a.住院次数 = b.住院次数
       AND   b.项目名称 = '氯化钠注射液（输液）')
AND EXISTS
      (SELECT * FROM 费用明细表 c
       WHERE a.id号 = c.id号
       AND   a.住院次数 = c.住院次数
       AND   c.项目名称 = '5%葡萄糖注射液')
AND a.项目名称 IN ('氯化钠注射液（输液）','5%葡萄糖注射液');

③

SELECT *
FROM 费用明细表 a 
WHERE (a.id号,a.住院次数) IN
      (SELECT b.id号,b.住院次数 
       FROM 费用明细表 b
       WHERE b.项目名称 IN ('氯化钠注射液（输液）','5%葡萄糖注射液')
       GROUP BY b.id号,b.住院次数
       HAVING COUNT(DISTINCT b.项目名称) = 2
      )
AND a.项目名称 IN ('氯化钠注射液（输液）','5%葡萄糖注射液');