postgresql 如何将两个查询的输出并排放置，并查看一个表在哪里缺少另一个表所拥有的记录(Postgres)

我有一个系统，其中“来源”生成门票，然后将它们发送到某个地方。
在我的postgres数据库中，我有3个表：

tickets AS j：关于货物的信息。列：标识票证、标识源、日期。
source AS b：关于源的信息。列：ID_源。
register AS c：票据发送状态信息。列：标识票证、标识源、日期。

注：请注意以下字段：一个月三个月一个月，一个月四个月一个月，一个月五个月一个月
生成票证时，票证元素存储在tickets AS j中。
每个来源每天最多可以生成一个票证。
有时票证生成失败，因此当天没有数据存储在tickets AS j中，因此将不存在具有j.date=that_day的票证
每一个源在一天结束时都试图将当天生成的票证发送到其他地方。

如果有一天没有当天的机票发送，什么都不会发生。（场景：无票据可发送）

因此，当天没有数据存储在register AS c中，因此不存在具有c.date=that_day的票证

如果某一天有当天的车票需要发送，但发送失败，则在表register AS c中填充一条来自tickets AS j的数据记录，并将c.sent设置为0。（场景：发送失败）
如果某一天有当天的车票需要发送，并且发送成功，则在表register AS c中填充一条来自tickets AS j的数据记录，并将c.sent设置为1。（场景：发送成功）

每天我都想知道每个源发生了什么情况。
为了做到这一点，我只能通过选择查询来工作，而不能创建更多的表。
此外，为了获得这些信息，我希望只运行一个查询，以获得当天源行为的全局视图。
我开发了以下查询，它返回场景“sending successed”的所有结果：
对于成功发送票据的所有源，它显示票据的源ID和状态，并且在发送失败或票据生成失败的情况下，它不返回任何记录。

SELECT b.id_source, c.sent 
FROM register AS c 
    JOIN tickets AS j ON c.date=j.date and c.id_ticket=j.id_ticket and c.idsource=j.idsource   
    LEFT JOIN source AS b ON j.idsource=b.id_source  
WHERE c.date=current_date-1;

我想操纵这个查询并将其与

SELECT b.id_source 
FROM source AS b;

以获得如下输出：

如果票据生成失败：显示b.id_source，显示c的无效数据。已发送
如果票生成成功且发送失败：显示b.id_源，显示c.已发送（0）
如果票生成成功且发送失败：显示b.id_源，显示c.已发送（1）

这是一个检查表。
为了执行此操作，我尝试将JOIN和LEFT JOIN替换为完全外部JOIN

SELECT b.id_source, c.sent 
FROM register AS c 
    FULL OUTER JOIN tickets AS j ON c.date=j.date and c.id_ticket=j.id_ticket and c.idsource=j.idsource   
    FULL OUTER JOIN source AS b ON j.idsource=b.id_source  
WHERE c.date=current_date-1;

但它似乎不起作用，或者可能是我选择了不正确的数据来做测试。
哪个查询可以完成这项工作？

我知道，对于给定的日期，您希望每个源对应一行，而列则说明票据是否正确生成和发送。
如果是这样，我建议从source表开始使用left join;显然，票证需要先创建，然后才能发送，因此这是我们将遵循的连接顺序：

select b.id, c.sent
from source as b
left join tickets as j 
    on  j.idsource = b.idsource 
    and j.date = current_date - 1
left join register as c 
    on  c.idsource = j.idsource 
    and c.date = j.date
    and c.id_ticket = j.id_ticket

这保证每个源一行;请注意，我将日期过滤移到了left join的on子句中，这样就不会过滤掉没有日票的源。
如果我们想在给定的时间段内得到相同的结果（每个日期和每个数据源一行），我们可以使用一个日期序列对数据源进行cross join，这样就可以对过去7天进行检查：

select b.id, d.dt, c.sent
from source as b
cross join generate_series(current_date - interval '1 week', current_date, interval '1 day') d(dt)
left join tickets as j 
    on  j.idsource = b.idsource 
    and j.date = d.dt
left join register as c 
    on  c.idsource = j.idsource 
    and c.date = j.date
    and c.id_ticket = j.id_ticket
order by d.id, d.dt

postgresql 如何将两个查询的输出并排放置，并查看一个表在哪里缺少另一个表所拥有的记录(Postgres)

1条答案

相关问题

热门标签

最新问答