原SQL:
SELECT
COUNT(DISTINCT a.customerid)
FROM
sl_sales_record a LEFT JOIN
(SELECT
customerid
FROM
sl_sales_record
WHERE
shopid = 55
AND regdate <= "2020-10-14"
AND regdate > "2020-06-14"
GROUP BY customerid) b
ON a.customerid = b.customerid
WHERE b.customerid IS NULL
AND a.shopid = 55
AND a.regdate <= "2020-06-14"
AND a.regdate > "2019-10-14"
不用临时表之后:
SELECT
COUNT(DISTINCT a.customerid)
FROM
sl_sales_record a LEFT JOIN
sl_sales_record b
on a.customerid = b.customerid
and b.shopid = 55
AND b.regdate <= "2020-10-14"
AND b.regdate > "2020-06-14"
WHERE b.customerid IS NULL
AND a.shopid = 55
AND a.regdate <= "2020-06-14"
AND a.regdate > "2019-10-14"
副表的所有条件拼在on里头
主表的条件在where
然后副表isnull也在where