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中的别名来表示标题)

| agentname | todaysales | nosales | yesterdaysales | yesterdaynosales |

我已经尝试过使用errors，但是不断地获取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

Answer 2

使用条件聚合(即在增益函数(如SUM)中的布尔表达式)。

select 
  your_name as agentname,
  sum(case when date(created) = curdate() then sale_amount end) as todaysales, 
  sum(date(created) = curdate()) as todaynosales,
  sum(case when date(created) = curdate() - interval 1 day then sale_amount end)
    as yesterdaysales, 
  sum(date(created) = curdate() - interval 1 day) as yesterdaynosales
from mp_wp_sales_tracking 
where created >= curdate() - interval 1 day
group by agentname
order by agentname;