从隐式 JOIN 返回行，其中结果存在于两个表中或仅存在于一个表中

Question

我有一个 PostgreSQL-9.0.x 数据库来管理自动化测试环境。有一堆表包含各种静态数据（操作系统版本、测试名称等），名为“buildlist”和“buildlist”。 'osversmap'。但是，还有两个表包含经常更改的数据。第一个是“待处理”表，它实际上是一个测试队列，其中待处理的测试由测试系统自行选择，然后在测试运行完成时删除。第二个是“结果”表，其中包含生成的测试结果（正在进行和已完成）。

待处理表中的记录与结果表中的记录具有一对多关系（待处理中的每一行在结果中可以有 0 或更多行）。例如，如果没有测试系统自行分配挂起行，则结果中的关联行将为零，然后一旦分配挂起行，结果中的行数将针对每个挂起行而增加。一个额外的问题是，我总是只想要与每个待处理表行关联的最新结果表行。我需要做的是查询“待处理”表中的待处理测试，然后从结果表中获取与每个待处理表行相对应的“logurl”。

所有这些都与这个问题非常相似，除了我有两个带有静态数据的附加表（buildlist 和 osversmap）的额外负担： PHP/SQL：仅使用一个查询，如果数据都在两个表中，则从两个表中选择行，如果没有，则仅从一个表中选择

我正在迷茫于如何将这两个表与静态数据集成到查询。只要“结果”表中至少有一行与挂起表中的每一行相对应，以下查询就可以正常工作（但是，对于仅存在于“挂起”中但尚未存在于“挂起”表中的行，它不会返回任何内容） '结果'）：

SELECT 
 pending.cl,
 pending.id,
 pending.buildid,
 pending.build_type,
 pending.active,
 pending.submittracker,
 pending.os,pending.arch,
 pending.osversion,
 pending.branch,
 pending.comment,
 osversmap.osname,
 buildlist.buildname,
 results.logurl 
FROM pending ,osversmap ,buildlist ,results 
WHERE 
 pending.buildid=buildlist.id 
 AND pending.os=osversmap.os 
 AND pending.osversion=osversmap.osversion 
 AND pending.owner='$owner' 
 AND pending.completed='f' 
 AND results.hostname=pending.active 
 AND results.submittracker=pending.submittracker 
 AND pending.cl=results.cl 
 AND results.current_status!='PASSED' 
 AND results.current_status NOT LIKE '%FAILED' 
ORDER BY pending.submittracker,pending.branch,pending.os,pending.arch

提前致谢！

Answer 1

以下内容对您有用吗？

SELECT  pending.cl,
        pending.id,
        pending.buildid,
        pending.build_type,
        pending.active,
        pending.submittracker,
        pending.os,
        pending.arch,
        pending.osversion,
        pending.branch,
        pending.comment,
        osversmap.osname,
        buildlist.buildname,
        results.logurl
FROM pending
    JOIN osversmap
        ON ( pending.os = osversmap.os
            AND pending.osversion = osversmap.osversion )
    JOIN buildlist
        ON ( pending.buildid = buildlist.id )
    LEFT OUTER JOIN results
        ON ( pending.active = results.hostname
            AND pending.submittracker = results.submittracker
            AND pending.cl = results.cl 
            AND results.current_status != 'PASSED'
            AND results.current_status NOT LIKE '%FAILED'
            )
WHERE pending.owner = '$owner'
    AND pending.completed = 'f'
ORDER BY pending.submittracker,
    pending.branch,
    pending.os,
    pending.arch

Answer 2

所有这些都与这个问题非常相似，除了我有两个带有静态数据的附加表（buildlist 和 osversmap）的额外负担：

最简单的方法是构建一个返回正确行的视图，而不引用 buildlist 和奥斯维地图。然后将这两个表连接到视图中。

CREATE VIEW your-meaningful-view-name AS
SELECT 
 pending.cl,
 pending.id,
 pending.buildid,
 pending.build_type,
 pending.active,
 pending.submittracker,
 pending.os,pending.arch,
 pending.osversion,
 pending.branch,
 pending.comment,
 results.logurl 
FROM pending
-- No DDL or sample INSERT statements. You might need an outer join.
INNER JOIN results 
        ON (results.hostname=pending.active AND
            results.submittracker=pending.submittracker AND
            results.cl=pending.cl)
WHERE pending.owner='$owner' AND
      pending.completed='f' AND
      -- Are *both* these really necessary?
      results.current_status!='PASSED' AND
      results.current_status NOT LIKE '%FAILED' 

进而

SELECT  osversmap.osname, buildlist.buildname, ymvn.*,
FROM your-meaningful-view-name ymvn
INNER JOIN osversmap ON ymvn.os=osversmap.os  
                    AND ymvn.osversion=osversmap.osversion 
INNER JOIN buildlist ON ymvn.buildid=buildlist.id 
ORDER BY ymvn.submittracker, ymvn.branch, ymvn.os, ymvn.arch