如何在1命令中对SQL执行不同的操作(子查询)

Question

CREATE TABLE PRESCRIPTION ( DID VARCHAR2(20) CONSTRAINT Prescription_DID_NotNull NOT NULL, PID VARCHAR2(20) CONSTRAINT Prescription_PID_NotNull NOT NULL, PrescDT DATE CONSTRAINT Prescription_PrescDT_NotNull NOT NULL, PharName VARCHAR2(30) CONSTRAINT Prescription_PharName_NotNull NOT NULL, Tradename VARCHAR2(30) CONSTRAINT Prescription_TradeName_NotNull NOT NULL, Remarks VARCHAR(128), CONSTRAINT Prescription_PK PRIMARY KEY (DID, PID, PrescDT, PharName, TradeName), CONSTRAINT Prescription_FK1 FOREIGN KEY (DID) REFERENCES DOCTOR (DID), CONSTRAINT Prescription_FK2 FOREIGN KEY (PID) REFERENCES PATIENT (PID), CONSTRAINT Prescription_FK3 FOREIGN KEY (PharName, Tradename) REFERENCES DRUG (PharName, Tradename) );

-- Insert sample records to the relational table PRESCRIPTION INSERT INTO PRESCRIPTION VALUES('3292812','S5503507C', sysdate-3,'Biomed','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292812','S4657986F', sysdate-21,'Medmed Shine','Zovirax','Apply to the infected areas 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3292812','S6335549H', sysdate-21,'Medcineco','Prednisone','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292812','7546975624', sysdate-3,'Kleenxin','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292812','7546975624', sysdate-3,'Biomed','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292812','S5436485D', sysdate-21,'Medmed Shine','Corticosteroids','Inhale when attack'); INSERT INTO PRESCRIPTION VALUES('3292812','S5434018K', sysdate-21,'Biomed','Silvadene','Apply to the affected area 3 times daily' ); INSERT INTO PRESCRIPTION VALUES('3292812','S5434018K', sysdate-21,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal' ); INSERT INTO PRESCRIPTION VALUES('3292812','S3338992D', sysdate-21,'Medcineco','Panadol','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183671','S5315919S', sysdate-19,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183671','S6125888E', sysdate-19,'Biomed','Acetaminophen','Apply to the affected areas 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3183671','S5225164A', sysdate-19,'Kleenxin','Propranolol','1 tablet daily after breakfast'); INSERT INTO PRESCRIPTION VALUES('3183671','S5234165A', sysdate-19,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S5311385K', sysdate-19,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S4535694G', sysdate-9,'Peter Martin','Dextromethorphan', '1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S4535694G', sysdate-9,'Biomed','Expectorants', '1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S6694865S', sysdate-2,'Medmed Shine','Chloramphenicol','Apply 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3292885','S5503507C', sysdate-14,'Kleenxin','Propranolol','1 tablet daily after breakfast'); INSERT INTO PRESCRIPTION VALUES('3292885','S8104586F', sysdate-14,'Peter Martin','Beclomethasone','Inhave when affected'); INSERT INTO PRESCRIPTION VALUES('3292885','B366548', sysdate-14,'Medmed Shine','Anxiolytic','1 tablet daily'); INSERT INTO PRESCRIPTION VALUES('3292885','S5476812D', sysdate-14,'Peter Martin','Aspirin','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S6654782F', sysdate-10,'Medmed Shine','Acetaminophen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3292885','S6654782F', sysdate-10,'Kleenxin','Salt water','Gargling frequently with warm, salt water'); INSERT INTO PRESCRIPTION VALUES('3292848','B765598', sysdate-1,'Kleenxin','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S6125888E', sysdate-21,'Medmed Shine','Neosporin','Apply to the affected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', sysdate-21,'Medmed Shine','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', sysdate-21,'Medcineco','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S4573521A', sysdate-20,'Kleenxin','Codeine','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S4573521A', sysdate-20,'Medcineco','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', sysdate-1,'Peter Martin','Panadol','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3293063','S5234165A', sysdate-19,'Kleenxin','Beclomethasone','Inhale when affected'); INSERT INTO PRESCRIPTION VALUES('3293063','S6458795J', sysdate-19,'Kleenxin','Beclomethasone','Inhave when affected'); INSERT INTO PRESCRIPTION VALUES('3293063','S5815447L', sysdate-19,'Peter Martin','Methicillin','Apply to the infected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3293063','S4535694G', sysdate-18,'Peter Martin','Prednisone','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S6575880E', sysdate-18,'Kleenxin','Naproxen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S6575880E', sysdate-18,'Medcineco','Ibuprofen','2 to 3 tablets every 4 to 6 hours'); INSERT INTO PRESCRIPTION VALUES('3332949','S5540866J', sysdate-18,'Kleenxin','Acetaminophen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5540866J', sysdate-18,'Kleenxin','Salt water','Gargling frequently with warm, salt water'); INSERT INTO PRESCRIPTION VALUES('3332949','S4531852D', sysdate-17,'Medmed Shine','Ibuprofen','Arrange for physical therapy.'); INSERT INTO PRESCRIPTION VALUES('3332949','S6694865S', sysdate-17,'Medmed Shine','Neosporin','Apply to the affected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3257095','S3994647E', sysdate-17,'Peter Martin','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257095','S5672186F', sysdate-17,'Peter Martin','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3231951','S6694865S', sysdate-16,'Kleenxin','Zovirax','Apply to the infected areas 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3231951','S3338992D', sysdate-16,'Medmed Shine','Aspirin','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5567841E', sysdate-16,'Medcineco','Emadine','Apply 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3332949','S3338992D', sysdate-15,'Peter Martin','Corticosteroids','Inhale when attack'); INSERT INTO PRESCRIPTION VALUES('3332949','S5311385K', sysdate-15,'Medcineco','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5311385K', sysdate-15,'Biomed','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5540866J', sysdate-15,'Medcineco','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S3338992D', sysdate-14,'Peter Martin','Codeine','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S3338992D', sysdate-14,'Biomed','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5567841E', sysdate-14,'Kleenxin','Naproxen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3332949','S5567841E', sysdate-14,'Kleenxin','Ibuprofen','1 tablet, 3 times daily after meal.'); INSERT INTO PRESCRIPTION VALUES('3332950','S6575880E', sysdate-14,'Medmed Shine','Ibuprofen','Arrange for physical therapy'); INSERT INTO PRESCRIPTION VALUES('3332950','S6550748A', sysdate-13,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3233339','S6694865S', sysdate-13,'Biomed','Penicillin','Apply to the infected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3233339','S4573521A', sysdate-13,'Kleenxin','Propranolol','1 tablet daily after breakfast'); INSERT INTO PRESCRIPTION VALUES('3233339','S5434018K', sysdate-13,'Medcineco','Emadine','Apply 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3233339','S5672186F', sysdate-13,'Peter Martin','Beclomethasone','Inhale when affected'); INSERT INTO PRESCRIPTION VALUES('3233339','S5434018K', sysdate-13,'Peter Martin','Salicylic acid','Apply to the infected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3183671','S5784263H', sysdate-12,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183671','S5311385K', sysdate-12,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183671','S3994647E', sysdate-12,'Peter Martin','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183671','S3994647E', sysdate-12,'Kleenxin','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S6125888E', sysdate-12,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S6225319E', sysdate-12,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5234165A', sysdate-12,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S6575880E', sysdate-11,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-11,'Biomed','Acetaminophen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-11,'Biomed','Doxycycline','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5570143E', sysdate-10,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5614758D', sysdate-10,'Peter Martin','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5614758D', sysdate-10,'Kleenxin','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-9,'Biomed','Acetaminophen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-9,'Biomed','Doxycycline','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5815447L', sysdate-9,'Kleenxin','Naproxen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5815447L', sysdate-9,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal.'); INSERT INTO PRESCRIPTION VALUES('3257338','S6458795J', sysdate-9,'Medcineco','Silvadene','Apply to the affected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3257338','S6458795J', sysdate-9,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5234165A', sysdate-7,'Biomed','Cromolyn sodium','Apply 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-7,'Biomed','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3257338','S5540866J', sysdate-7,'Medmed Shine','Doxycycline','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S5570143E', sysdate-7,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S5672186F', sysdate-6,'Biomed','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S5672186F', sysdate-6,'Kleenxin','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S6458795J', sysdate-6,'Biomed','Dextromethorphan','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S6458795J', sysdate-6,'Kleenxin','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3183646','S5570143E', sysdate-6,'Medmed Shine','Cyclophosphamide','Procedure is to be done at the hospital.'); INSERT INTO PRESCRIPTION VALUES('3217643','S6458795J', sysdate,'Kleenxin','Prednisone','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3217546','S5311385K', sysdate,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3217546','S5311385K', sysdate,'Medmed Shine','Codeine','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3217546','S5311385K', sysdate,'Biomed','Expectorants','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3232974','S5815447L', sysdate,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3232974','S3994647E', sysdate,'Medmed Shine','Neosporin','Apply to the affected area 3 times daily'); INSERT INTO PRESCRIPTION VALUES('3232974','S5234165A', sysdate,'Medcineco','Azithromycin','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3232974','S6575880E', sysdate,'Kleenxin','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3254117','S5225164A', sysdate,'Kleenxin','Opioids','1 tablet, 3 times daily after meal'); INSERT INTO PRESCRIPTION VALUES('3254117','S5784263H', sysdate,'Medmed Shine','Ibuprofen','1 tablet, 3 times daily after meal');

我想找到这种药(贸易名)，它只开了一个月和一年，我的条件是，只要它已经开了3次/月，我就把它列出来。

提前谢谢。

Answer 1

对您的问题的直接回答似乎是使用having子句进行聚合：

SELECT FRUIT, TO_CHAR(DATEOFPURCHASE, 'YYYY-MM') as yyyymm
FROM TABLE
GROUP BY FRUIT, TO_CHAR(DATEOFPURCHASE, 'YYYY-MM')
HAVING COUNT(*) = 3;

然而，你的样本结果表明，真正的问题是:在几个月内购买了三个不同的水果，哪些是水果？对于这个问题，您可以使用分析函数：

SELECT FRUIT, TO_CHAR(DATEOFPURCHASE, 'YYYY-MM') as yyyymm
FROM (SELECT t.*,
             COUNT(DISTINCT fruit) OVER (PARTITION BY TO_CHAR(DATEOFPURCHASE, 'YYYY-MM')) as numfruit
      FROM TABLE t
     ) t
WHERE numfruit = 3;

Answer 2

解决示例数据中的几个问题：

1. DDL有引用表的外键，您还没有描述--我删除了这些引用。
2. 示例数据使用SYSDATE --任何运行查询的人都将得到不同的答案，这取决于他们运行查询的日期。我用一个固定的日期代替了它。

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE PRESCRIPTION (
    DID         VARCHAR2(20)
        CONSTRAINT Prescription_DID_NotNull NOT NULL,
    PID         VARCHAR2(20)
        CONSTRAINT Prescription_PID_NotNull NOT NULL,
    PrescDT     DATE
        CONSTRAINT Prescription_PrescDT_NotNull NOT NULL,
    PharName        VARCHAR2(30)
        CONSTRAINT Prescription_PharName_NotNull NOT NULL,
    Tradename       VARCHAR2(30)
        CONSTRAINT Prescription_TradeName_NotNull NOT NULL,
    Remarks     VARCHAR(128),
CONSTRAINT Prescription_PK PRIMARY KEY (DID, PID, PrescDT, PharName, TradeName)
);

-- Insert sample records to the relational table PRESCRIPTION
INSERT INTO PRESCRIPTION VALUES('3292812','S5503507C', TO_DATE( '150101','YYMMDD')+3,'Biomed','Opioids','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292812','S4657986F', TO_DATE( '150101','YYMMDD')+21,'Medmed Shine','Zovirax','Apply to the infected areas 3 times daily');
INSERT INTO PRESCRIPTION VALUES('3292812','S6335549H', TO_DATE( '150101','YYMMDD')+21,'Medcineco','Prednisone','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292812','7546975624', TO_DATE( '150101','YYMMDD')+3,'Kleenxin','Dextromethorphan','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292812','7546975624', TO_DATE( '150101','YYMMDD')+3,'Biomed','Expectorants','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292812','S5436485D', TO_DATE( '150101','YYMMDD')+21,'Medmed Shine','Corticosteroids','Inhale when attack');
INSERT INTO PRESCRIPTION VALUES('3292812','S5434018K', TO_DATE( '150101','YYMMDD')+21,'Biomed','Silvadene','Apply to the affected area 3 times daily' );
INSERT INTO PRESCRIPTION VALUES('3292812','S5434018K', TO_DATE( '150101','YYMMDD')+21,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal' );
INSERT INTO PRESCRIPTION VALUES('3292812','S3338992D', TO_DATE( '150101','YYMMDD')+21,'Medcineco','Panadol','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3183671','S5315919S', TO_DATE( '150101','YYMMDD')+19,'Medmed Shine','Opioids','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3183671','S6125888E', TO_DATE( '150101','YYMMDD')+19,'Biomed','Acetaminophen','Apply to the affected areas 3 times daily');
INSERT INTO PRESCRIPTION VALUES('3183671','S5225164A', TO_DATE( '150101','YYMMDD')+19,'Kleenxin','Propranolol','1 tablet daily after breakfast');
INSERT INTO PRESCRIPTION VALUES('3183671','S5234165A', TO_DATE( '150101','YYMMDD')+19,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S5311385K', TO_DATE( '150101','YYMMDD')+19,'Biomed','Ibuprofen','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S4535694G', TO_DATE( '150101','YYMMDD')+9,'Peter Martin','Dextromethorphan', '1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S4535694G', TO_DATE( '150101','YYMMDD')+9,'Biomed','Expectorants', '1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S6694865S', TO_DATE( '150101','YYMMDD')+2,'Medmed Shine','Chloramphenicol','Apply 3 times daily');
INSERT INTO PRESCRIPTION VALUES('3292885','S5503507C', TO_DATE( '150101','YYMMDD')+14,'Kleenxin','Propranolol','1 tablet daily after breakfast');
INSERT INTO PRESCRIPTION VALUES('3292885','S8104586F', TO_DATE( '150101','YYMMDD')+14,'Peter Martin','Beclomethasone','Inhave when affected');
INSERT INTO PRESCRIPTION VALUES('3292885','B366548', TO_DATE( '150101','YYMMDD')+14,'Medmed Shine','Anxiolytic','1 tablet daily');
INSERT INTO PRESCRIPTION VALUES('3292885','S5476812D', TO_DATE( '150101','YYMMDD')+14,'Peter Martin','Aspirin','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S6654782F', TO_DATE( '150101','YYMMDD')+10,'Medmed Shine','Acetaminophen','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3292885','S6654782F', TO_DATE( '150101','YYMMDD')+10,'Kleenxin','Salt water','Gargling frequently with warm, salt water');
INSERT INTO PRESCRIPTION VALUES('3292848','B765598', TO_DATE( '150101','YYMMDD')+1,'Kleenxin','Opioids','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S6125888E', TO_DATE( '150101','YYMMDD')+21,'Medmed Shine','Neosporin','Apply to the affected area 3 times daily');
INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', TO_DATE( '150101','YYMMDD')+21,'Medmed Shine','Dextromethorphan','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', TO_DATE( '150101','YYMMDD')+21,'Medcineco','Expectorants','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S4573521A', TO_DATE( '150101','YYMMDD')+20,'Kleenxin','Codeine','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S4573521A', TO_DATE( '150101','YYMMDD')+20,'Medcineco','Expectorants','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S6654782F', TO_DATE( '150101','YYMMDD')+1,'Peter Martin','Panadol','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3293063','S5234165A', TO_DATE( '150101','YYMMDD')+19,'Kleenxin','Beclomethasone','Inhale when affected');
INSERT INTO PRESCRIPTION VALUES('3293063','S6458795J', TO_DATE( '150101','YYMMDD')+19,'Kleenxin','Beclomethasone','Inhave when affected');
INSERT INTO PRESCRIPTION VALUES('3293063','S5815447L', TO_DATE( '150101','YYMMDD')+19,'Peter Martin','Methicillin','Apply to the infected area 3 times daily');
INSERT INTO PRESCRIPTION VALUES('3293063','S4535694G', TO_DATE( '150101','YYMMDD')+18,'Peter Martin','Prednisone','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3332949','S6575880E', TO_DATE( '150101','YYMMDD')+18,'Kleenxin','Naproxen','1 tablet, 3 times daily after meal');
INSERT INTO PRESCRIPTION VALUES('3332949','S6575880E', TO_DATE( '150101','YYMMDD')+18,'Medcineco','Ibuprofen','2 to 3 tablets every 4 to 6 hours');

查询1

-- I want to find the drug(tradename), with only the month and year it has
-- been prescribed and my condition is to list the drug if only it has been
-- prescribed more than 3 times per month.

SELECT Tradename,
       TRUNC( PrescDT, 'MM' ) AS Month
FROM   PRESCRIPTION
GROUP BY Tradename, TRUNC( PrescDT, 'MM' )
HAVING COUNT(1) > 3

结果

|    TRADENAME |                     MONTH |
|--------------|---------------------------|
|    Ibuprofen | January, 01 2015 00:00:00 |
| Expectorants | January, 01 2015 00:00:00 |