关于从left join 变换到笛卡尔连接

话不多说 直接上代码

select * from(SELECT Datepart(month, start.add_time) AS month,
       Count(DISTINCT( start.id ))     AS count,
       Count(CASE
               WHEN distribution_processing = 215 THEN 1
             END)                      AS gxd_count,
       Count(CASE
               WHEN distribution_processing = 48 THEN 1
             END)                      AS jxz_count,
       Count(CASE
               WHEN distribution_processing = 247 THEN 1
             END)                      AS jzy_count,
       Count(CASE
               WHEN distribution_processing = 244 THEN 1
             END)                      AS yxy_count,
       Count(CASE
               WHEN distribution_processing = 266 THEN 1
             END)                      AS zry_count,
       Count(CASE
               WHEN distribution_processing = 268 THEN 1
             END)                      AS zyf_count,
       Count(CASE
               WHEN Datediff(ss, start.add_time, stop.add_time) > ( CASE
                                                                      WHEN expected.task_timeout_hour_4 != 0 THEN ( expected.task_timeout_hour_4 - 0.5 ) * 3600
                                                                      WHEN expected.task_timeout_hour_4 = 0 THEN expected.task_timeout_hour_4 * 3600
                                                                    END )THEN 1
             END)                      AS timeout_count
FROM   yh_account_record
       LEFT JOIN yh_account_log AS start
              ON start.acc_id = yh_account_record.id
                 AND start.state_type_start = 50
                 AND start.state_type = 4
       LEFT JOIN yh_account_log AS stop
              ON stop.acc_id = yh_account_record.id
                 AND stop.state_type_start = 4
                 AND stop.state_type = 11
       LEFT JOIN yh_account_log AS cancel
              ON cancel.acc_id = yh_account_record.id
                 AND cancel.state_type_start = 4
                 AND cancel.state_type = 8
       LEFT JOIN yh_exchange_member
              ON yh_account_record.exc_bro_id = yh_exchange_member.id
       LEFT JOIN yh_exchange AS expected
              ON yh_exchange_member.exc_id = expected.id
WHERE  yh_account_record.distribution_processing is not null
       AND start.add_time BETWEEN '2020-1-1' AND '2020-12-31'
GROUP  BY Datepart(month, start.add_time) )as start,

(select Datepart(month, cancel.add_time) as month,
       Count(DISTINCT( cancel.id ))    AS invalid_count

       from  yh_account_record
       LEFT JOIN yh_account_log AS cancel
              ON cancel.acc_id = yh_account_record.id
                 AND cancel.state_type_start = 4
                 AND cancel.state_type = 8

WHERE  yh_account_record.distribution_processing is not null
       AND cancel.add_time BETWEEN '2020-1-1' AND '2020-12-31'
GROUP  BY Datepart(month, cancel.add_time) )as cancel where start.month=cancel.month

上述代码是对两个查询结果做笛卡尔连接 同时用where语句过滤掉无用数据

SELECT Datepart(month, start.add_time) AS month,
       Count(DISTINCT( start.id ))     AS count,
       Count(CASE
               WHEN distribution_processing = 215 THEN 1
             END)                      AS gxd_count,
       Count(CASE
               WHEN distribution_processing = 48 THEN 1
             END)                      AS jxz_count,
       Count(CASE
               WHEN distribution_processing = 247 THEN 1
             END)                      AS jzy_count,
       Count(CASE
               WHEN distribution_processing = 244 THEN 1
             END)                      AS yxy_count,
       Count(CASE
               WHEN distribution_processing = 266 THEN 1
             END)                      AS zry_count,
       Count(CASE
               WHEN distribution_processing = 268 THEN 1
             END)                      AS zyf_count,
       Count(CASE
               WHEN Datediff(ss, start.add_time, stop.add_time) > ( CASE
                                                                      WHEN expected.task_timeout_hour_4 != 0 THEN ( expected.task_timeout_hour_4 - 0.5 ) * 3600
                                                                      WHEN expected.task_timeout_hour_4 = 0 THEN expected.task_timeout_hour_4 * 3600
                                                                    END )THEN 1
             END)                      AS timeout_count,
       Count(DISTINCT( cancel.id ))    AS invalid_count
FROM   yh_account_record
       LEFT JOIN yh_account_log AS start
              ON start.acc_id = yh_account_record.id
                 AND start.state_type_start = 50
                 AND start.state_type = 4
       LEFT JOIN yh_account_log AS stop
              ON stop.acc_id = yh_account_record.id
                 AND stop.state_type_start = 4
                 AND stop.state_type = 11
       LEFT JOIN yh_account_log AS cancel
              ON cancel.acc_id = yh_account_record.id
                 AND cancel.state_type_start = 4
                 AND cancel.state_type = 8
       LEFT JOIN yh_exchange_member
              ON yh_account_record.exc_bro_id = yh_exchange_member.id
       LEFT JOIN yh_exchange AS expected
              ON yh_exchange_member.exc_id = expected.id
WHERE  yh_account_record.distribution_processing is not null
       AND convert(char(10),start.add_time,23) BETWEEN '2020-1-1' AND '2020-12-31'
GROUP  BY Datepart(month, start.add_time) 

上述代码是通过对数据做左链接，同时通过on关键字控制链接后的结果集，通过where关键字筛选输出结果。

两者最大的不同在于，前者的代码稍微变长了一点，但是对于条件的作用更加的独立，直接作用在查询结果上，比如对于分月统计每月的xx状态数据以及yy状态数据，通过左链接的sql语句where 条件只能写在单一条件上，而使用笛卡尔积的sql语句可以使where 语句独立作用在自己的个体上。