mysql - 用总和案例获取个人数字，月度数据问题

Question

我正在计算按 GST 占总销售额的百分比明智地按月支付的 GST。它为我提供了正确的数据，但在应用条件时，它为我提供的是年度数据而不是月度数据。

在下面的代码中，我没有使用 "AND p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18"

但我认为它给了我错误的年度 GST 总金额数据，因为在产品级别，只有一些产品没有映射到 GST 板，所以我在 YTD 总 GST 中添加了上面的行，只得到那些有 5-每年 12-18 次在现场，效果很好。

我得到了 gst_mt_dtotal正确，但为了确保如果将来在订单表中添加的任何未映射到 GST 板的产品不应该被计算在内，我在 gst_mtd 中添加了相同的代码。 (每月)也是.. 令我惊讶的是，它给出的数字与每年给我的数字相同，这是错误的，我不明白为什么会这样？任何人都可以帮忙吗？？

我是否使用正确的方法来明智地计算 GST 税额？？请指导。

SELECT 
--yearly
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_total_ytd,


SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 5
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_5_ytd,
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 12
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_12_ytd,
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_18_ytd,
   --monthly 
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_total_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 5
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_5_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 12
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_12_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_18_mtd    
FROM
   pos_order_products op
   LEFT JOIN pos_orders o ON o.order_id = op.pos_order_id
   LEFT JOIN product p ON p.id = op.product_id
   AND op.is_deleted = 0
   AND o.is_deleted = 'N'

Answer 1

您需要像这样将 OR 条件放在大括号中 - (p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18)

SELECT 
--yearly
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND (p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18)
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_total_ytd,


SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 5
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_5_ytd,
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 12
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_12_ytd,
SUM(CASE 
    WHEN o.created_on >= CONCAT(YEAR(NOW()), '-04-01')
    AND o.created_on  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR
    AND p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_18_ytd,
   --monthly 
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND (p.gst_tax = 5 || p.gst_tax = 12 || p.gst_tax = 18)
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_total_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 5
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_5_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 12
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_12_mtd,
SUM(CASE
    WHEN YEAR(o.created_on) = YEAR(CURRENT_DATE())
    AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
    AND p.gst_tax = 18
    THEN op.gst_tax_value ELSE 0 end) as pos_gst_18_mtd    
FROM
   pos_order_products op
   LEFT JOIN pos_orders o ON o.order_id = op.pos_order_id
   LEFT JOIN product p ON p.id = op.product_id
   AND op.is_deleted = 0
   AND o.is_deleted = 'N'