mysql - 如何对3个不同日期的子查询进行减法和除法？

Question

我想用日期过滤器减去两个子查询，然后将它们除以最后购买的日期

这是了解 7 月目标销售额的第一个查询:

select floor(sum(uorpd_qty * 20/100 + (uorpd_qty))) '20%'
from user_order_product
left join user_order on user_order_product.uor_id = user_order.uor_id 
where uor_status = 'completed'
and uorpd_is_free = 0
and date_format(uor_date, '%Y-%m-%d') >= '2019-07-01'
and date_format(uor_date, '%Y-%m-%d') <  '2019-08-01'

然后第二次查询以了解 8 月份的总销售额:

select sum(uorpd_qty)
from user_order_product
left join user_order on user_order_product.uor_id = user_order.uor_id 
where uor_status = 'completed'
and uorpd_is_free = 0 
and date_format(uor_date, '%Y-%m-%d') >= '2019-08-01'
and date_format(uor_date, '%Y-%m-%d') <  '2019-09-01'

然后第三个查询来了解八月的最后购买日期:

select datediff(now(),max(uor_date))
from user_order_product
left join user_order on user_order_product.uor_id = user_order.uor_id 
where uor_status = 'completed'
and uorpd_is_free = 0
and date_format(uor_date, '%Y-%m-%d') >= '2019-08-01'
and date_format(uor_date, '%Y-%m-%d') <  '2019-09-01'

如何减去第一个查询和第二个查询，然后除以第三个查询？

select sum(-uorpd_qty) + (select floor(sum(uorpd_qty * 20/100 + (uorpd_qty))) '20%'
                         from user_order_product
                          left join user_order on user_order_product.uor_id = user_order.uor_id 
                          where uor_status = 'completed'
                         and uorpd_is_free = 0
                         and date_format(uor_date, '%Y-%m-%d') >= '2019-07-01'
                         and date_format(uor_date, '%Y-%m-%d') <  '2019-08-01')tar
from user_order_product
left join user_order on user_order_product.uor_id = user_order.uor_id 
where uor_status = 'completed'
and uorpd_is_free = 0 
/ (select datediff(now(),max(uor_date))
from user_order_product
left join user_order on user_order_product.uor_id = user_order.uor_id 
where uor_status = 'completed'
and uorpd_is_free = 0)divii
[[AND date_format(uor_date,'%Y-%m-%d') >= {{start_date}}]]
[[AND date_format(uor_date,'%Y-%m-%d') <= {{end_date}}]]

select floor(sum(uorpd_qty * 20/100 + (uorpd_qty))) '20%'
from user_order_productenter code here
left join user_order on user_order_product.uor_id = user_order.uor_id
where uor_status = 'completed'
and uorpd_is_free = 0
and date_format(uor_date, '%Y-%m-%d') >= '2019-07-01'
and date_format(uor_date, '%Y-%m-%d') < '2019-08-01'

Answer 1

由于您只在 1 个表上进行计算，我想使用 CASE 语句可以完成预期结果 -

SELECT (FLOOR(SUM(CASE WHEN DATE_FORMAT(uor_date, '%Y-%m-%d') >= '2019-07-01' 
                        AND DATE_FORMAT(uor_date, '%Y-%m-%d') <  '2019-08-01'
                            THEN uorpd_qty * 20/100 + uorpd_qty END)) - 
        SUM(CASE WHEN date_format(uor_date, '%Y-%m-%d') >= '2019-08-01'
                  AND date_format(uor_date, '%Y-%m-%d') <  '2019-09-01'
                      THEN uorpd_qty)) / 
        CASE WHEN date_format(uor_date, '%Y-%m-%d') >= '2019-08-01'
              AND date_format(uor_date, '%Y-%m-%d') <  '2019-09-01'
                  THEN DATEDIFF(NOW(),MAX(uor_date)) '20%'
FROM user_order_product UOP
LEFT JOIN user_order UO on UOP.uor_id = UO.uor_id
WHERE uor_status = 'completed'
AND uorpd_is_free = 0

没有测试数据，很难测试此查询，但它可能会解决您的问题。