我有一个表，它有两列ID和Date，就像下面的数据一样。对于给定的范围，比如从2022-09-01到2022-09-10，我想返回各个ID的缺失日期以及ID值，我希望返回预期输出中提到的数据。我如何实现这个
表内数据：
| ID | 日期 |
|---|---|
| 1 | 2022-09-01 |
| 1 | 2022-09-07 |
| 1 | 2022-09-08 |
| 1 | 2022-09 |
| 2 | 2022-09-01 |
| 2 | 2022-09-02 |
| 2 | 2022-09-03 |
| 2 | 2022-09-04 |
我为您编写了示例查询：
CREATE TABLE test1 (
id int4 NULL,
pdate date NULL
);
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-01');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-07');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-08');
INSERT INTO test1 (id, pdate) VALUES(1, '2022-09-09');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-01');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-02');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-03');
INSERT INTO test1 (id, pdate) VALUES(2, '2022-09-04');
select t1.id, t1.datelist from (
select t.id, generate_series(t.startdate, t.enddate, '1 day')::date as datelist from (
select distinct id, '2022-09-01'::date as startdate, '2022-09-10'::date as enddate from test1
) t
) t1
left join test1 t2 on t2.pdate = t1.datelist and t1.id = t2.id
where t2.pdate is null
Result:
id datelist
1 2022-09-02
1 2022-09-03
1 2022-09-04
1 2022-09-05
1 2022-09-06
1 2022-09-10
2 2022-09-05
2 2022-09-06
2 2022-09-07
2 2022-09-08
2 2022-09-09
2 2022-09-10