有两个表,
DoctorSeesPatient: (D1,P1),(D1,P2),(D2,P1),(D3,P1),(D3,P2),(D3,P3),(D4,P2),(D5,P1)
DoctorWorksAt: (D1,H1),(D2,H1),(D3,H1),(D4,H2),(D5,H3)现在想查出表中,搜出病人与医院(病人访问过医院里所有医生)结果应该如下
Result=(P1,H1),(P2,H2),(P1,H3)可是为什么我的语句不能正常运行呢...会有多余的数据create or replace view nq12_v1(patient,doctor,hospital) as
select s.patient, w.doctor, w.hospital from nq12_DoctorSeesPatient s, nq12_DoctorWorksAt w
where s.doctor = w.doctor
order by s.patient, w.hospital
;select distinct v.patient, v.hospital
from nq12_v1 v
where NOT EXISTS(
(select distinct doctor from nq12_DoctorSeesPatient
where patient = v.patient
and doctor in(
select doctor from nq12_DoctorWorksAt
where hospital = v.hospital
)
)
minus
(select distinct doctor from nq12_DoctorWorksAt
where hospital = v.hospital
)
)
;
DoctorSeesPatient: (D1,P1),(D1,P2),(D2,P1),(D3,P1),(D3,P2),(D3,P3),(D4,P2),(D5,P1)
DoctorWorksAt: (D1,H1),(D2,H1),(D3,H1),(D4,H2),(D5,H3)现在想查出表中,搜出病人与医院(病人访问过医院里所有医生)结果应该如下
Result=(P1,H1),(P2,H2),(P1,H3)可是为什么我的语句不能正常运行呢...会有多余的数据create or replace view nq12_v1(patient,doctor,hospital) as
select s.patient, w.doctor, w.hospital from nq12_DoctorSeesPatient s, nq12_DoctorWorksAt w
where s.doctor = w.doctor
order by s.patient, w.hospital
;select distinct v.patient, v.hospital
from nq12_v1 v
where NOT EXISTS(
(select distinct doctor from nq12_DoctorSeesPatient
where patient = v.patient
and doctor in(
select doctor from nq12_DoctorWorksAt
where hospital = v.hospital
)
)
minus
(select distinct doctor from nq12_DoctorWorksAt
where hospital = v.hospital
)
)
;
解决方案 »
- oracle 调用存储过程 动态sql错误
- oralce 并发处理
- Oracle9i: SQL与PL/SQL开发指南 习题答案
- struts2+hibernate+spring中加入oracle(10g) 的CLOB字段出现的错误!
- oracle中SID能改名吗???在线等?
- 游标的使用,大虾们进来看看那!!
- oracle里怎么创建普通用户?
- oralce9i自带的jdk是什么版本?能否给它升级成高的版本?
- 寻找oracle8i 数据库的在线帮助文档
- linux下安装oracle不出现下一步
- 高级复制,更新物化视图300万数据没问题,更新500万就发现主表没被push回数据????急
- PL/SQL里QR_DBMS包里的of_back_exec代码怎么看???
(select Patient,Hospital,count(*) as DoctorNum
from nq12_v1
group by Patient,Hospital) a ,
(select Hospital,count(*) as DoctorNum
from nq12_DoctorWorksAt
group by Hospital) b
where a.Hospital = b.Hospital
and a.DoctorNum = b.DoctorNum ;
SELECT DISTINCT v.patient, v.hospital
FROM nq12_v1 v
WHERE NOT EXISTS (
(SELECT doctor
FROM doctorworksat w
WHERE NOT EXISTS (
SELECT doctor
FROM doctorseespatient s
WHERE s.doctor = w.doctor
AND s.patient = v.patient)
AND w.hospital = v.hospital))