计算两个子查询( mysql )之间的百分比

Question

我试图计算两个子查询之间的百分比，我有一个解决方案，在我看来，这个解决方案一点也不优雅：

SET @tot := (select count(*) FROM shipment WHERE created < date_format(date_add(CURRENT_TIMESTAMP(), interval 1 day), '%Y%m%d000000') AND created >= date_format(CURRENT_TIMESTAMP(), '%Y%m%d000000'))

SELECT @tot AS Total, ((select count(*) from shipment where created < date_format(date_add(CURRENT_TIMESTAMP(), interval 1 day), '%Y%m%d000000') AND created >= date_format(CURRENT_TIMESTAMP(), '%Y%m%d000000') AND state = 'despatched')  / @tot) * 100 AS Percentage

虽然这在命令行中有效，但在我试图为其创建报告的定制平台上却失败了，我想知道是否有一种方法可以在不使用set变量的情况下简化这一点？

提前谢谢。

Answer 1

我不认为有任何理由不能简单地将其作为嵌套子查询中的选择。(未在MySQL中进行测试，但在Server中使用适当的日期函数。)

select total, dispatched / total * 100 as percentage from
(
  select count(*) as Total, 
    sum(case when state = 'despatched' then 1.0 else 0.0 end) as dispatched
  from 
    shipment
  where 
    created >= date_format(CURRENT_TIMESTAMP(), '%Y%m%d000000') 
  and 
    created < date_format(date_add(CURRENT_TIMESTAMP(), interval 1 day), '%Y%m%d000000')
) calcs