在postgres中合并多个查询
我有2个查询。 查询1: -在postgres中合并多个查询
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
尝试使用UNION ALL,但将无法正常工作。建议任何替代方法来合并它们。
既然你在这两个查询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;
是的,我喜欢这种想法。可能适合我!谢谢。 –
获取#1064 - 您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册,以便在第一行使用'labor as(SELECT mr.id AS mrid,count(ml.id)AS labor_cnt FROM'附近的正确语法' –
'您将此标签标记为'postgresql',所以我使用了postgres的语法,我不确定mysql的等效语法是什么, – jmelesky
你叫什么结合?..结果如何查找这两个查询? –