在Postgres中结合多个查询

Question

我有2个查询。查询1： -

select mr.id,count(ml.id) as labor_cnt 
from mreq Mr 
join mlbr ml on Mr.id = ml.mrid 
where Mr.id in(1235,3355) 
group by Mr.id

查询2： -

select mr.id,count(mm.id) as mtrial_cnt 
from mreq Mr join mmrm mm on Mr.id = mm.mrid 
where Mr.id in(1235,3355) 
group by Mr.id

试图使用所有工会，但行不通。建议将它们组合的任何替代方法。

Answer 1

由于您在两个查询中都通过mr.id进行分组，我认为您想要结果：

 mr.id | labor_cnt | mtrial_cnt
--------------------------------
     1 |         5 |          3
     2 |      null |          6
     3 |         4 |          2
     4 |         3 |       null
...

如果您要寻找的是，那么您可以将查询与常见的表格相结合。类似：

WITH labor as (
  SELECT mr.id AS mrid, count(ml.id) AS labor_cnt
    FROM mreq mr JOIN mlbr ml ON mr.id = ml.mrid
    WHERE mr.id IN (1235, 3355)
  GROUP BY mr.id),
mtrial as (
  SELECT mr.id AS mrid, count(mm.id) AS mtrial_cnt
    FROM mreq mr JOIN mmrm mm ON mr.id = mm.mrid
    WHERE mr.id in (1235, 3355)
  GROUP BY mr.id)
SELECT COALESCE(l.mrid, m.mrid), l.labor_cnt, m.labor_cnt
  FROM labor l FULL OUTER JOIN mtrial m ON mrid
ORDER BY mrid;

编辑为添加

看起来您正在使用mySQL，而MySQL不支持常见的表格表达式。MySQL确实支持子征服，因此可能有效（注意：我没有验证语法，因为我没有运行的mySQL实例）：

SELECT COALESCE(l.mrid, m.mrid), l.labor_cnt, m.labor_cnt
  FROM 
    (SELECT mr.id AS mrid, count(ml.id) AS labor_cnt
       FROM mreq mr JOIN mlbr ml ON mr.id = ml.mrid
       WHERE mr.id IN (1235, 3355)
     GROUP BY mr.id) AS labor
    FULL OUTER JOIN
    (SELECT mr.id AS mrid, count(mm.id) AS mtrial_cnt
       FROM mreq mr JOIN mmrm mm ON mr.id = mm.mrid
       WHERE mr.id in (1235, 3355)
     GROUP BY mr.id) AS mtrial
    ON mrid
ORDER BY mrid;