如何将两条不同Select查询的结果作为多列合并到同一条查询（不使用union）

你可以直接使用条件聚合实现需求，仅扫描一次表即可得到所有字段结果，性能最优，完全不需要用到UNION：

SELECT 
    DATE(created_at) AS stat_date,
    COUNT(CASE WHEN status = 'Approved' THEN id END) AS lead,
    SUM(CASE WHEN status = 'Approved' THEN publisher_earned ELSE 0 END) AS earning,
    COUNT(id) AS clicks
FROM offer_process 
WHERE 
    publisher_id = '1738' 
    AND created_at >= '2021-08-01'
GROUP BY DATE(created_at)

逻辑说明 #

• 按DATE(created_at)分组保证结果按天对齐，和你原有两个查询的分组逻辑完全一致
• lead、earning两个字段仅统计status='Approved'的对应数据，和第一个原始查询逻辑完全匹配
• clicks字段统计当前publisher、指定时间范围内的每日总记录数，和第二个原始查询逻辑完全匹配

如果后续你遇到两个查询的数据源来自不同表的场景，可以使用子查询关联的方案实现列合并：

SELECT 
    COALESCE(t1.stat_date, t2.stat_date) AS stat_date,
    COALESCE(t1.lead, 0) AS lead,
    COALESCE(t1.earning, 0) AS earning,
    COALESCE(t2.clicks, 0) AS clicks
FROM (
    SELECT 
        DATE(created_at) AS stat_date,
        COUNT(id) AS lead,
        SUM(publisher_earned) AS earning 
    FROM offer_process 
    WHERE 
        status='Approved'  
        AND publisher_id='1738' 
        AND created_at>='2021-08-01' 
    GROUP BY DATE(created_at)
) AS t1
FULL OUTER JOIN (
    SELECT 
        DATE(created_at) AS stat_date,
        COUNT(id) AS clicks 
    FROM offer_process  
    WHERE   
        publisher_id='1738'  
        AND created_at>='2021-08-01' 
    GROUP BY DATE(created_at)
) AS t2 ON t1.stat_date = t2.stat_date

使用FULL OUTER JOIN可以避免某一天只有点击没有有效转化、或者只有转化没有点击时的数据丢失问题。

内容的提问来源于stack exchange，提问作者Habib Ur Rehman