根据多列按日期时间获取最后一条记录

Question

想知道如何在postgresql中做某事我有一个表有

AcctNo    EntryDate        EntryTime   Form    FormSeq   anscode   answer
123456    8/14/20           07:05      ORREC    0001      ans1      1245
123456    8/13/20           07:07      ORREC    0001      ans1      1235
123456    8/14/20           07:04      ORREC    0001      ans2      test
123456    8/14/20           07:05      ORREC    0001      ans2      testing
123456    8/15/20           12:15      ORREC    0002      ans1      1520
123456    8/15/20           12:17      ORREC    0002      ans1      1522

我需要的是根据日期和时间获得每个表单的最后一个条目/formseq/anscode

所以我得到的三个结果是

AcctNo    EntryDate       EntryTime   Form    FormSeq   anscode   answer
123456    8/14/20          07:05      ORREC    0001      ans1      1245
123456    8/14/20          07:05      ORREC    0001      ans2      testing
123456    8/15/20          12:17      ORREC    0002      ans1      1522
select AcctNo,EntryDate,EntryTime,Form,FormSeq,anscode,answer 
from TableName 
where       
Group By AcctNo,Form,FormSeq,Anscode

Answer 1

下面是一个完整的示例。它与您的数据输出不太匹配。但它确实符合"根据日期和时间获取每个表单的最后一个条目/formseq/anscode"。因此，它返回每个日期的最后一个条目。如果您希望每个表单的最后一个输入周期/formseq/anscode是不同的查询。

CREATE TABLE public.test_table (
acctno integer,
entrydate date,
entrytime time without time zone,
form character varying,
formseq character varying,
anscode character varying,
answer character varying
);
COPY public.test_table (acctno, entrydate, entrytime, form, formseq, anscode, answer) FROM stdin;
123456  2020-08-14      07:05:00        ORREC   0001    ans1    1245
123456  2020-08-13      07:07:00        ORREC   0001    ans1    1235
123456  2020-08-14      07:04:00        ORREC   0001    ans2    test
123456  2020-08-14      07:05:00        ORREC   0001    ans2    testing
123456  2020-08-15      12:15:00        ORREC   0002    ans1    1520
123456  2020-08-15      12:17:00        ORREC   0002    ans1    1522
.
SELECT
distinct on(entrydate, form, formSeq, anscode)  acctno,entrydate,entrytime,form,formSeq,anscode,answer
from
test_table
WHERE
acctNo = 123456
ORDER BY
entrydate , form, formseq, anscode, entrytime desc;
acctno | entrydate  | entrytime | form  | formseq | anscode | answer  
--------+------------+-----------+-------+---------+---------+---------
123456 | 2020-08-13 | 07:07:00  | ORREC | 0001    | ans1    | 1235
123456 | 2020-08-14 | 07:05:00  | ORREC | 0001    | ans1    | 1245
123456 | 2020-08-14 | 07:05:00  | ORREC | 0001    | ans2    | testing
123456 | 2020-08-15 | 12:17:00  | ORREC | 0002    | ans1    | 1522

第二个输出类似数据的查询示例：

SELECT DISTINCT ON (form, formseq, anscode)
acctno,
entrydate,
entrytime,
form,
formseq,
anscode,
answer
FROM
test_table
ORDER BY
form,
formseq,
anscode,
entrydate + entrytime DESC;
acctno | entrydate  | entrytime | form  | formseq | anscode | answer  
--------+------------+-----------+-------+---------+---------+---------
123456 | 2020-08-14 | 07:05:00  | ORREC | 0001    | ans1    | 1245
123456 | 2020-08-14 | 07:05:00  | ORREC | 0001    | ans2    | testing
123456 | 2020-08-15 | 12:17:00  | ORREC | 0002    | ans1    | 1522