使用oracle数据库创建视图 [英] Create a view with oracle database
问题描述
请注意,如何为第一季度(2016年1月1日至3月31日)的特定医生创建一个给定患者在给定日期的总费用的视图。应该给出视图的输出。
我尝试过:
我试图从DOCTOR中选择DID,
来自PATIENT的SELECT PID,
来自APPOINTMEnT的SELECT n,其中n> = 01-JAN-16< = 31-MAR-16
并且不收取费用(从治疗中选择不同的费用);
然后我使用view命令:
创建视图总费用AS SELECT查询
SeleCT PID,DID,DATE,来自待遇的费用;
--- #DOCTOR表---
CREATE TABLE #Doctor(
DID INT,PID INT,DNAME VARCHAR(50));
INSERT INTO #Doctor
VALUES(1215,1209,'harish'),(1234,1261,'Saikiran'),
(1245,1271,'satish'),(1246, 1214, 'Santoshkumar');
------#patient TABLE ---
CREATE TABLE #patient(
PID INT,P_name VARCHAR(50),APMID INT,收取MONEY DEFAULT 500) ;
INSERT INTO #patient
VALUES(1271,'satya',1,2000),(1261,'masthan',2,500),
(1209,'Varma',3,500),( 1214, 'SREE',4600);
------#AppointMent表-----
CREATE TABLE #AppointMent(
APMID INT,APMDATE DATE,DID INT)
INSERT INTO #AppointMent
VALUES(3,'21 / MAR / 2017',1215),(2,'03 / JUL / 2017',1234),
(1, '05 / JAN / 2017',1245),(4,'08 / FEB / 2017',1246)
----在查询下创建视图----
SELECT#PATIENT.PID,#DOCTOR.DID,#APPOINTMENT.APMDATE,SUM(#PATIENT.charges)作为费用
FROM #DOCTOR内部联接#PATIENT ON(#DOCTOR.PID =#PATIENT.PID )
内连接#APPOINTMENT
ON(#APCOOINTMENT.APMID =#PATIENT.APMID)和(#APCOOINTMENT.DID =#Doctor.DID)
WHERE APMDATE> = '01 -JAN-2017'和APMDATE< = '31 -MAR-2017'
GROUP BY#PATIENT.PID,#DOCTOR.DID,#APPOINTMENT.APMDATE
拥有SUM(#PATIENT.charges)<>(选择500作为TREATMENTCHARGES) ;
--------------------------------
PID DID APMDATE收费
- ------------------------------
1214 1246 2017-02-08 600.00
1271 1245 2017- 01-05 2000.00
Please, how do I create a view that will give total charges for a given patient on a given day for a given doctor for the first quarter(January 1 through March 31, 2016). The output of the view should be given.
What I have tried:
I tried to SELECT DID from DOCTOR,
SELECT PID from PATIENT,
SELECT n from APPOINTMEnT where n > = 01-JAN-16 and < = 31-MAR-16
AND CHARGES NOT IN (SELECT DISTINCT CHARGES FROM TREATMENT);
then I use the view command as:
CREATE VIEW totalcharge AS SELECT query
SeleCT PID, DID, DATE, CHARGES from treatment;
---#DOCTOR Table --- CREATE TABLE #Doctor( DID INT,PID INT,DNAME VARCHAR(50)); INSERT INTO #Doctor VALUES(1215,1209,'harish'),(1234,1261,'Saikiran'), (1245,1271,'satish'),(1246,1214,'Santoshkumar'); ------#patient TABLE --- CREATE TABLE #patient ( PID INT,P_name VARCHAR(50),APMID INT,charges MONEY DEFAULT 500); INSERT INTO #patient VALUES(1271,'satya',1,2000),(1261,'masthan',2,500), (1209,'Varma',3,500),(1214,'sree',4,600); ------#AppointMent Table ----- CREATE TABLE #AppointMent( APMID INT,APMDATE DATE,DID INT) INSERT INTO #AppointMent VALUES(3,'21/MAR/2017',1215),(2,'03/JUL/2017',1234), (1,'05/JAN/2017',1245),(4,'08/FEB/2017',1246) ----Create View on Below Query ---- SELECT #PATIENT.PID, #DOCTOR.DID,#APPOINTMENT.APMDATE,SUM(#PATIENT.charges)as charges FROM #DOCTOR inner join #PATIENT ON(#DOCTOR.PID=#PATIENT.PID) inner join #APPOINTMENT ON(#APPOINTMENT.APMID=#PATIENT.APMID)and(#APPOINTMENT.DID=#Doctor.DID) WHERE APMDATE> ='01-JAN-2017' and APMDATE< ='31-MAR-2017' GROUP BY #PATIENT.PID,#DOCTOR.DID,#APPOINTMENT.APMDATE HAVING SUM(#PATIENT.charges)<>(select 500 as TREATMENTCHARGES); -------------------------------- PID DID APMDATE charges -------------------------------- 1214 1246 2017-02-08 600.00 1271 1245 2017-01-05 2000.00
这篇关于使用oracle数据库创建视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!