在Postgresql中水平连接两个SELECT查询

Question

我有以下两个查询：

查询 #1：

(SELECT 
    pl.c_project_Id, pl.c_projectphase_Id, pl.c_projecttask_Id, pl.m_product_Id,
    pj.name as projectname, ph.name as phasename, pt.name as taskname, pd.name as prodname,
    round(pl.plannedqty, 2) as planqty, round(pl.plannedprice, 2) as planrate,
    round(pl.plannedamt, 2) as planamt

FROM adempiere.c_projectline pl
LEFT JOIN adempiere.c_project pj ON pl.c_project_id = pj.c_project_id
LEFT JOIN adempiere.c_projectphase ph ON pl.c_projectphase_id = ph.c_projectphase_id
LEFT JOIN adempiere.c_projecttask pt ON pl.c_projecttask_id = pt.c_projecttask_id
LEFT JOIN adempiere.m_product pd ON pl.m_product_id = pd.m_product_id
WHERE pl.c_project_id = 1000001 AND pl.ad_client_id = 1000000
ORDER BY ph.c_projectphase_id, pt.c_projecttask_id)

输出结果为：11列16行。

查询 #2：

(SELECT
    fa.c_project_id, fa.c_projectphase_id, fa.c_projecttask_id, fa.m_product_id,
    pj.name as costprojectname, ph.name as costphasename, pt.name as costtaskname,
    pd.name as costprodname,
    abs(fa.qty) as costqty, round((fa.amtacctdr/fa.qty), 2) as costrate,
    round(sum(fa.amtacctdr), 0) as costamt

FROM adempiere.fact_acct fa
LEFT JOIN adempiere.c_project pj ON fa.c_project_id = pj.c_project_id
LEFT JOIN adempiere.c_projectphase ph ON fa.c_projectphase_id = ph.c_projectphase_id
LEFT JOIN adempiere.c_projecttask pt ON fa.c_projecttask_id = pt.c_projecttask_id
LEFT JOIN adempiere.m_product pd ON fa.m_product_id = pd.m_product_id
WHERE  fa.c_project_id = 1000001 AND (fa.gl_category_id = 1000006 OR fa.gl_category_id = 1000005)
AND fa.qty > 0 AND fa.c_project_id is not null
GROUP BY fa.m_product_id, fa.c_project_id, fa.c_projectphase_id, fa.c_projecttask_id,
        fa.qty, fa.amtacctdr,
        pj.name, ph.name, pt.name, pd.name)

输出是：11列6行

我想水平连接这些查询，显示所有列，但不应重复行。当我应用union将它们连接时，结果会显示重复的行。我该如何解决这个问题？

- see2rizwan

---

你如何匹配查询之间的行？ - Mureinik

Answer 1

你应该能够像这样联接查询：

select * from
  (
   <your first query here>
  ) tbl1
  join (
    <your second query here>
  ) tbl2
  on tbl1.c_project_Id = tbl2.c_project_Id
 and tbl1.c_projectphase_Id = tbl2.c_projectphase_Id -- you might add or
 and tbl1.c_projecttask_Id  = tbl2.c_projecttask_Id  -- remove join criteria 
 and tbl1.m_product_Id = tbl2.m_product_Id           -- here

- alzaimar

---

感谢您的回复。我尝试了您的解决方案，但它显示了所有列和仅符合所有条件的6行。但是我需要来自第一个查询的所有16行，并且从第二个查询中获取符合条件的列（costqty、costrate、aostamt）的值，否则显示零。我该怎么办？ - see2rizwan

请改用“全联接”。只显示了6行的原因很可能是只有6行共享相同的“联接”条件。因此，在“join”关键字之前添加“full”，并检查结果。 - alzaimar

是的，“FULL JOIN”正在工作，我得到了非常准确的结果。非常感谢您的帮助。 - see2rizwan

直到我在这里看了一下https://dev59.com/cWQn5IYBdhLWcg3wrouL，我才明白这个查询。 - Raymond Chenon