组合我的两个MySQL查询
组合我的两个MySQL查询
提问于 2017-07-08 21:44:19
这是erp.orders：
userId paidAt
1 2017-06-30
1 2017-06-18
2 2017-06-07
4 2017-06-07
3 2017-01-01
2 2017-01-01
2 2017-01-01
2 2017-01-01我是prod.referral_order_delivered
user_id
1
2
1
1
1我需要合并两个请求。
这一请求：
SELECT DISTINCT
erp.orders.userid, COUNT(erp.orders.userid) as countorders
FROM
erp.orders
WHERE
erp.orders.userid IN (SELECT erp.orders.userid
FROM erp.orders, prod.referral_order_delivered
WHERE erp.orders.userid = prod.referral_order_delivered.user_id
AND erp.orders.paidat::date >= '2017-06-07'
AND erp.orders.paidat::date <= '2017-07-07')
GROUP BY
erp.orders.userid;这将返回以下结果：
userId countorders
1 2
2 4而这一请求：
SELECT
prod.referral_order_delivered.user_id,
COUNT(prod.referral_order_delivered.user_id) AS countreferral
FROM
prod.referral_order_delivered
WHERE
prod.referral_order_delivered.user_id IN (SELECT DISTINCT erp.orders.userid
FROM erp.orders
INNER JOIN prod.referral_order_delivered ON erp.orders.userid = prod.referral_order_delivered.user_id
WHERE erp.orders.paidat >= '2017-06-07'
AND erp.orders.paidat <= '2017-07-07')
GROUP BY
prod.referral_order_delivered.user_id返回此结果：
user_id countreferral
1 4
2 1 现在，我想将这些请求组合起来，以获得以下结果：
userId countorders countreferral
1 2 4
2 4 1请注意，user_id和userId是相同的。所以不管这是user_id还是userId。
您可以测试解决方案这里。
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-07-09 01:44:45
考虑将两个聚合查询合并为派生表。首先，将IN子句查询(这两个查询与前者等效，使用隐式联接和后者使用显式联接)转换为JOIN子句。然后，运行相同的聚合，外部查询将两者连接起来，并选择列。
SELECT agg1.userid, agg1.countorders, agg2.countreferral
FROM
(SELECT j.userid, COUNT(j.userid) as countorders
FROM
(SELECT DISTINCT erp.orders.userid
FROM erp.orders
INNER JOIN prod.referral_order_delivered
ON erp.orders.userid = prod.referral_order_delivered.user_id
WHERE erp.orders.paidat >= '2017-06-07'
AND erp.orders.paidat <= '2017-07-07') j
INNER JOIN erp.orders e ON j.userid = e.userid
GROUP BY j.userid) agg1
INNER JOIN
(SELECT j.userid, COUNT(j.userid) as countreferral
FROM
(SELECT DISTINCT erp.orders.userid
FROM erp.orders
INNER JOIN prod.referral_order_delivered
ON erp.orders.userid = prod.referral_order_delivered.user_id
WHERE erp.orders.paidat >= '2017-06-07'
AND erp.orders.paidat <= '2017-07-07') j
INNER JOIN prod.referral_order_delivered p ON j.userid = p.user_id
GROUP BY j.userid) agg2
ON agg1.userid = agg2.userid甚至将两个聚合源中使用的嵌套内部查询保存为视图。
SELECT agg1.userid, agg1.countorders, agg2.countreferral
FROM
(SELECT v.userid, COUNT(j.userid) as countorders
FROM myview v
INNER JOIN erp.orders e ON v.userid = e.userid
GROUP BY v.userid) agg1
INNER JOIN
(SELECT v.userid, COUNT(j.userid) as countreferral
FROM myview v
INNER JOIN prod.referral_order_delivered p ON v.userid = p.user_id
GROUP BY v.userid) agg2
ON agg1.userid = agg2.userid在其他关系数据库管理系统中，就像Postgres一样，与MySQL不同，您可以使用CTE的WITH()来避免重复。请参见初始设置中的雷克斯试验器演示分叉。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44991008
复制相关文章
相似问题
腾讯云开发者
