我试图在MySQL中创建一个报告（来自Wordpress/woomerce安装中的表，但是这个商店征税的方式不同于woomerce做事的方式，所以我不能使用woomerce报告）
计算需要从总额中计算出20%的税率（英国增值税），但前提是客户在特定地点。所以，如果客户在“世界”中，税率是0%。如果客户在英国，税率为20%。但收费总额不变（因此出口利润高于本地销售额！）
我从：
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
给了我一张这样的桌子：
Date | Order no | Location | Order Total
May 2013 | 123 |World |1124.00
Jan 2013 | 124 |UK |163.00
到现在为止，一直都还不错。所以我试着加上一些金额来计算增值税的金额。
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
我想这会给我：
Date | Order no | Location | Order Total | VAT | NET
May 2013 | 123| World | 1124.00 | 187.33 | 1311.33
Jan 2013 | 124 | UK | 163.00 | 27.17 | 190.17
然后我可以继续尝试如何按位置更改速率。
不幸的是，它似乎总结了一切，而不是给我一行行的结果。
1）我搞砸了什么，把所有的东西都加在一起了？
2）关于如何最好地应用增值税计算的任何想法，仅当运输方法标题=英国时？
3）很明显，这只在税率改变前有效。我最好还是在下次修改税率时按日期限制此报告，并为适用新税率的未来订单编制新报告？还是有更聪明的方法？
我很感激向客户收取统一税率并缴纳可变税率是一种稍微有点奇怪的做法，但我没有改变这一点，我只是负责提供报告。:-(
编辑
感谢Gordon，我已经解决了SUM问题，现在我的查询如下所示，并生成了我所期望的表布局，如下所示：
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
GROUP BY wp_posts.ID
但我仍然不确定如何根据location字段中的值更改乘数。可能吧，还是我找错树了？
编辑2
我已经找到了一个部分的解决方案——而不是我用一个案例给我一个增值税的计算值。
CASE
when m2.meta_value = 'World Delivery' then 0
when m2.meta_value = 'UK Delivery' then (m3.meta_value*20/120)
when m2.meta_value = 'European Delivery' then (m3.meta_value*20/120)
END AS _VAT
不幸的是，我不能对净价值（price-VAT，因为它和VAT是同一种情况，所以创建了一个错误）重复CASE语句。
我想做点什么
SUM(_order_total-_VAT) AS _NET
但是*_order_total*和*_VAT*不被视为可以计算的列。
最终编辑
再次感谢Gordon，我的最后一个查询是这样的，我只是添加它以防其他人发现它有帮助。这个查询与Wordpress导出和报告插件一起工作，因此可以很快集成到Wordpress管理中并导出到Excel中，这很漂亮。
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Customer_Location,
m3.meta_value AS _order_total,
ROUND(SUM(case when m2.meta_value = 'World Delivery' then 0 else m3.meta_value*20/120 end),2) AS _VAT,
ROUND(SUM(case when m2.meta_value = 'World Delivery' then m3.meta_value else m3.meta_value*100/120 end),2) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order' AND wp_comments.comment_post_id = wp_posts.ID AND wp_comments.comment_content="Order status changed from processing to completed."
GROUP BY wp_posts.ID
最佳答案
您只得到一行的原因是因为sum()函数使其成为聚合查询。但是，你没有group by。我认为按posted分组可以修复这个问题，并对select进行一些其他调整：
SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
max(m1.meta_value) AS _order_number,
max(m2.meta_value) AS _Customer_Location,
max(m3.meta_value) AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
这类数据很难习惯。一条记录的所有数据被拆分到多行中。
要根据位置更改乘数，请使用
where子句。下面是一个例子：SUM(case when location = 'UK' then 0 else m3.meta_value*20/120 end) AS _VAT,
SUM(case when location = 'UK' then m3.meta_value else m3.meta_value*100/120 end) AS _NET
关于mysql - 在MySQL中计算总计的增值税/税金-增值税率取决于位置字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16680486/