时间戳

首页 > 解决方案 > 左连接与子查询

sql - 左连接与子查询

问题描述

我有以下带有左连接的查询我可以将其作为子查询吗？因为它是同一张桌子，它会更快地工作吗？我只想获取收入行并仅为他们显示收入2

SELECT *
FROM
    (SELECT 
         s_campaign_id,
         SUM(CASE
                WHEN d_gen_date BETWEEN '2021-01-31' AND '2021-01-31'
                   THEN f_revenue
                   ELSE 0
             END) AS revenue,
         SUM(CASE
                WHEN d_gen_date BETWEEN '2021-01-30' AND '2021-01-30' 
                   THEN f_revenue
                   ELSE 0
             END) AS revenue2
     FROM 
         tbl_reports
     WHERE 
         (d_gen_date >= '2021-01-31'
          AND d_gen_date <= '2021-01-31')
     GROUP BY 
         s_campaign_id) d1
LEFT JOIN
    (SELECT 
         s_campaign_id,
         SUM(CASE
                WHEN d_gen_date BETWEEN '2021-01-30' AND '2021-01-30' 
                   THEN f_revenue
                   ELSE 0
             END) AS revenue2
     FROM 
         tbl_reports
     WHERE 
         (d_gen_date BETWEEN '2021-01-30' AND '2021-01-30')
     GROUP BY 
         s_campaign_id) d2 ON d1.s_campaign_id = d2.s_campaign_id

标签： sql

解决方案

您的查询非常可疑，因为您可以使用第一个查询中的微小更改来获得所需的结果，如下所示：

  SELECT T.* FROM
  (SELECT s_campaign_id,
         SUM(CASE WHEN d_gen_date = '2021-01-31' THEN f_revenue ELSE 0 END) AS revenue,
         SUM(CASE WHEN d_gen_date = '2021-01-30' THEN f_revenue ELSE 0 END) AS revenue2 
    FROM tbl_reports 
   WHERE (d_gen_date>='2021-01-30' AND d_gen_date<='2021-01-31') 
   GROUP BY s_campaign_id) T
   WHERE EXISTS (select 1 from tbl_reports tt 
                  where t.s_campaign_id = tt.s_campaign_id
                    and tt.d_gen_date ='2021-01-31')

推荐阅读