mysql - 从两个表中计算销售额

Question

我正在使用 SUM ( CASE WHEN ) 来计算是和否的数量，它工作正常。

我在计算两个表中匹配手机号码的数量时遇到问题。它似乎没有正确计数。

dairy表有MobileNO字段，sales表有mobile字段

SELECT 
       D.Username, 
       SUM(CASE WHEN D.type = 'Yes' THEN 1 ELSE 0 END) as Yes, 
       SUM(CASE WHEN D.type = 'No' THEN 1 ELSE 0 END) as No, 
       SUM(CASE WHEN D.type = '' THEN 1 ELSE 0 END) as Other, 
       (SELECT SUM(CASE WHEN D.MobileNo = S.mobile THEN 1 ELSE 0 END) from sales as S) as Sales, 
       COUNT(*) as TOTAL FROM dairy as D 
WHERE source = 'Company' AND UNIX_TIMESTAMP(CheckDate) >= 1293840000 AND UNIX_TIMESTAMP(CheckDate) <= 1322697600 
group by D.Username order by TOTAL DESC 

Answer 1

SELECT 
       D.Username, 
       SUM(CASE WHEN D.type = 'Yes' THEN 1 ELSE 0 END) as Yes, 
       SUM(CASE WHEN D.type = 'No' THEN 1 ELSE 0 END) as No, 
       SUM(CASE WHEN D.type = '' THEN 1 ELSE 0 END) as Other, 
       SUM(CASE WHEN S.mobile IS NULL THEN 0 ELSE 1 END) as Sales, 
       COUNT(*) as TOTAL 
FROM dairy as D 
LEFT JOIN (SELECT DISTINCT mobile FROM sales) as S ON D.MobileNo = S.mobile
WHERE source = 'Company' AND UNIX_TIMESTAMP(CheckDate) >= 1293840000 AND UNIX_TIMESTAMP(CheckDate) <= 1322697600 
group by D.Username order by TOTAL DESC