mysql - 结合我的两个 MySQL 查询

Question

这是 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 都没有关系。

您可以测试解决方案 here 。

Answer 1

考虑将两个聚合查询连接为派生表。首先，将 IN 子句查询(两者都等同于前者使用 implicit join 和后者使用 explicit join)到 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

甚至将两个聚合源中使用的嵌套内部查询保存为 view .

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 等其他 RDBMS 中，与 MySQL 不同，您可以使用 CTE 的 WITH() 来避免重复。参见 rextester demo从您的初始设置中 fork 出来。