mysql - 一个查询中的税收(累积或常规税收)

Question

mysql> select * from phppos_sales_items_taxes;
+---------+---------+------+-------+---------+------------+
| sale_id | item_id | line | name  | percent | cumulative |
+---------+---------+------+-------+---------+------------+
|       1 |       1 |    1 | Tax 1 |   10.00 |          0 |
|       1 |       1 |    1 | Tax 2 |   10.00 |          1 |
|       3 |       1 |    1 | Tax 1 |   10.00 |          0 |
|       3 |       1 |    1 | Tax 2 |   10.00 |          0 |
|       4 |       1 |    1 | Tax 1 |    8.00 |          0 |
|       4 |       1 |    1 | Tax 2 |   10.00 |          1 |
+---------+---------+------+-------+---------+------------+

mysql> select * from phppos_sales_items;
+---------+---------+-------------+--------------+------+--------------------+-----------------+-----------------+------------------+
| sale_id | item_id | description | serialnumber | line | quantity_purchased | item_cost_price | item_unit_price | discount_percent |
+---------+---------+-------------+--------------+------+--------------------+-----------------+-----------------+------------------+
|       1 |       1 |             |              |    1 |               1.00 |            8.00 |           10.00 |                0 |
|       3 |       1 |             |              |    1 |               1.00 |            8.00 |           10.00 |                0 |
|       4 |       1 |             |              |    1 |               3.00 |            8.00 |           10.00 |                0 |
|       5 |       2 |             |              |    1 |               1.00 |           10.00 |           12.00 |                0 |
+---------+---------+-------------+--------------+------+--------------------+-----------------+-----------------+------------------+
4 rows in set (0.00 sec)

现在对于每个百分比(10.00、8.00 等)，我需要在一次查询中确定总税额。如果税收累计 = 1，则税收由(小计 + 其他销售税)* tax_percent/100 确定，否则为标准税收公式。

SELECT percent, 
       SUM(subtotal) AS subtotal, 
       SUM(total)    AS total, 
       SUM(tax)      AS tax 
FROM   (SELECT name, 
               Concat(percent, '%') 
               AS 
                      PERCENT, 
               ( item_unit_price * quantity_purchased - 
                 item_unit_price * quantity_purchased * 
                 discount_percent / 100 ) AS subtotal, 
               IF (cumulative = 1, Round(( item_unit_price * quantity_purchased 
                                           - 
                                           item_unit_price * quantity_purchased 
                                           * 
                                           discount_percent / 100 + 
--NEED TO DO A SUBQUERY HERE TO GET SALES TAX FOR FOR SALE_ID, ITEM_ID, LINE


 ) * ( 
                                                             percent / 100 ), 2) 
               , Round 
               (( 
               item_unit_price * quantity_purchased - 
               item_unit_price * quantity_purchased * 
               discount_percent 
               / 100 ) * ( 1 + ( percent / 100 ) ), 2)) 
               AS 
                      total, 
               Round(( item_unit_price * quantity_purchased - 
                       item_unit_price * quantity_purchased * 
                             discount_percent / 100 ) * 
                     ( percent / 100 ), 2) 
               AS tax 
        FROM   phppos_sales_items_taxes 
               JOIN phppos_sales_items 
                 ON phppos_sales_items.sale_id = 
                    phppos_sales_items_taxes.sale_id 
                    AND phppos_sales_items.item_id = 
                        phppos_sales_items_taxes.item_id 
                    AND phppos_sales_items.line = phppos_sales_items_taxes.line 
               JOIN phppos_sales 
                 ON phppos_sales_items_taxes.sale_id = phppos_sales.sale_id 
        WHERE  phppos_sales.deleted = 0 
               AND DATE(sale_time) BETWEEN '2011-07-28' AND '2011-07-28') AS 
       temp_taxes 
GROUP  BY percent 

Answer 1

尝试使用 IF 语句:

IF(cumulative = 1,(((subtotal+other_tax_in_sale)* tax_percent) / 100), standard_tax)

如果您填写值，这应该会起作用。