MYSQL 多选、Where 和 Group By 对同一个表

Question

我有两个 MYSQL 查询，我正在尝试将其合并为一个，这是第一个 -

SELECT 
  `Your_Name` as agentname,
  sum(`Sale_Amount`) as todaysales, 
  COUNT(*) as NoSales 
FROM mp_wp_sales_tracking 
WHERE `created` BETWEEN CURDATE() AND NOW() 
GROUP BY agentname

第二个是相同的查询，只是针对前一天

SELECT 
  `Your_Name` as agentname,
  sum(`Sale_Amount`) as yesterdaysales, 
  COUNT(*) as YesterdayNoSales 
FROM mp_wp_sales_tracking 
WHERE `created` BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 DAY) 
                    AND ADDTIME(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'23:59:59.50') 
GROUP BY agentname

现在我想做的是实现如下所示的内容(根据 MYSQL 中的别名的 header )

| agentname | todaysales | nosales | yesterdaysales | yesterdaynosales |

我已经尝试过使用 Inner Join，但不断收到 SQL 错误，我不知道在哪里打开它，因为对复杂的 MYSQL 查询相当陌生。

Answer 1

我可以想到一种方法来合并这两个查询并对结果使用条件聚合

SELECT `Your_Name` as agentname,
SUM( CASE WHEN 
    `created` BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND ADDTIME(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'23:59:59.50') 
    THEN `Sale_Amount`
    ELSE 0 END
    ) as yesterdaysales,
SUM(`created` BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND ADDTIME(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'23:59:59.50') ) as YesterdayNoSales ,
SUM(CASE WHEN 
    `created` BETWEEN CURDATE() AND NOW()
    THEN `Sale_Amount`
    ELSE 0 END
    ) as todaysales,
SUM(`created` BETWEEN CURDATE() AND NOW() ) as NoSales
FROM mp_wp_sales_tracking 
WHERE `created` BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND ADDTIME(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'23:59:59.50') 
       OR `created` BETWEEN CURDATE() AND NOW() 
GROUP BY agentname