sql - 需要帮助在 Postgresql 中编写 SQL 查询

Question

我一直在尝试编写此查询，但由于某种原因似乎无法正确完成..

我需要做的是:

如果在过去 24 小时内没有将与此问题相关的更新插入到 qUpdateTable 中，则将问题的状态更改为“已关闭”。

我只希望在工作人员至少回复一次时关闭它。

您可以通过检查 qUpdateTable 并查看 StaffID 字段是否为空或是否具有特定工单更新的值来确定工作人员或用户是否已回答问题。如果有 staffID，则表明它已被工作人员更新，但如果没有，则 qUpdate 是由用户完成的。

本质上，它的工作方式是用户通过插入到问题表中来发布问题，并通过插入到 qUpdate 表中并使用外键“QuestionID”链接到原始问题来进行回复。

表格:

CREATE TABLE Staff
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL
);

CREATE TABLE Customer
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL,
    Email       VARCHAR(40) NOT NULL
);

CREATE TABLE Product
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name    TEXT NOT NULL
);

CREATE TABLE Question
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Problem VARCHAR(1000),
    Status  VARCHAR(20) NOT NULL DEFAULT 'open', 
    Priority    INTEGER NOT NULL,
    LoggedTime  TIMESTAMP NOT NULL,
    CustomerID  INTEGER NOT NULL,
    ProductID   INTEGER NOT NULL,
    FOREIGN KEY (ProductID) REFERENCES Product(ID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
    CHECK (Status IN ('open','closed') AND Priority IN (1,2,3))

);

CREATE TABLE qUpdate
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Message VARCHAR(1000) NOT NULL,
    UpdateTime  TIMESTAMP NOT NULL,
    QuestionID  INTEGER NOT NULL,
    StaffID INTEGER,
    FOREIGN KEY (StaffID) REFERENCES Staff(ID),
    FOREIGN KEY (QuestionID) REFERENCES Question(ID)
);

一些样本插入:

INSERT INTO Customer (ID, Name, Email) VALUES (1, 'testname1', 'testemail1');

INSERT INTO Customer (ID, Name, Email) VALUES (2, 'testname2', 'testemail2');

INSERT INTO Staff (ID, Name) VALUES (1, 'Don Keigh');

INSERT INTO Product (ID, Name) VALUES (1, 'Xbox');

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (1, 'testproblem1', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (2, 'testproblem2', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, StaffID, QuestionID) VALUES (2, 'testmessage1','2012-07-12 14:27', 1, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, QuestionID) VALUES (3, 'testmessage1','2012-06-18 19:42', 2);

到目前为止我所做的(显然行不通)

UPDATE Question
SET Status = 'closed' 
WHERE EXISTS
(SELECT qUpdate.QuestionID  
MAX(qUpdate.UpdateTime - Now() = INTERVAL '1 day') FROM qUpdate
LEFT JOIN Question ON qUpdate.QuestionID = Question.ID
WHERE qUpdate.StaffID IS NOT NULL);

我知道我的解释可能有点困惑，所以如果您需要更多信息，我会尽快回复

Answer 1

UPDATE Question
SET Status = 'closed' 
where
-- this clause asserts there's at least one staff answer
exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and StaffID is not null
)
-- this clause asserts there's been no update in the last 24 hours
and not exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and qUpdate.UpdateTime > (now() - interval '24 hours') 
)
and Status = 'open';

您几乎肯定需要 qUpdate(QuestionID) 或可能 qUpdate(QuestionID,UpdateTime) 或 qUpdate(QuestionID,StaffID) 的索引，以便在 exists() 测试中的子选择上获得良好的性能。