我在使用Postgresql 9.4时遇到了一个问题,我希望程序只展示
创建的表格如下:
这是我遇到问题的陈述:
sum(v.price) > 1000
,但是如果我在where条件中输入total > 1000
,程序会提示total不存在,同时不允许我输入sum(v.price)
,因为在这个部分无法进行这种操作。创建的表格如下:
CREATE TABLE PATIENT
(
Pat_Number INTEGER,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(50) NOT NULL,
City VARCHAR(30) NOT NULL,
CONSTRAINT pk_PATIENT PRIMARY KEY (Pat_Number)
);
CREATE TABLE VISIT
(
Doc_Number INTEGER,
Pat_Number INTEGER,
Visit_Date DATE,
Price DECIMAL(7,2),
Turn INTEGER NOT NULL,
CONSTRAINT Visit_pk PRIMARY KEY (Doc_Number, Pat_Number, Visit_Date),
CONSTRAINT Visit_Doctor_fk FOREIGN KEY (Doc_Number) REFERENCES DOCTOR(Doc_Number),
CONSTRAINT Visit_PATIENT_fk FOREIGN KEY (Pat_Number) REFERENCES PATIENT(Pat_Number)
);
这是我遇到问题的陈述:
SELECT
p.name, p.address, p.city, sum(v.price) as total
FROM
VISIT v
JOIN
PATIENT p ON p.Pat_Number = v.Pat_Number
WHERE
Date(Visit_Date) < '01/01/2012'
GROUP BY
p.name, p.address, p.city, p.Pat_Number, v.Pat_Number
ORDER BY
total DESC;
我该怎么做呢?