我正在尝试在MySQL中创建一个报告(来自Wordpress / Woocommerce安装中的表格,但这个商店征税的方式与Woocommerce的工作方式不同,所以我不能使用Woocommerce报告 . )
计算需要从总数中计算出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
到现在为止还挺好 . 那么我尝试添加一些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'
我以为这会给我:
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
然后我可以继续尝试找出如何按位置更改费率 . 不幸的是,似乎SUM一切,而不是逐行给我结果 .
1)我搞砸了什么把它总结在一起?
2)只有在_shipping_method_title = UK时才能最好地应用增值税计算的任何想法?
3)显然,这只适用于税率变化 . 处理这个问题是我最好的选择,只是在下次修改税率时按日期限制此报告,并为新税率适用的未来订单制作新报告?还是有一种更聪明的方式?
我很感激,向客户收取固定费率并支付可变税是一种有点奇怪的方法,但我不能改变这一点,我只是负责提供报告 . :-(
EDIT 感谢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
但我仍然不确定如何根据位置字段中的值更改乘数 . 这可能,或者我在这里咆哮错误的树?
EDIT 2
我已经找到了部分解决方案 - 而不是SUM行,我使用CASE给出了增值税的计算值 .
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
不幸的是,我不能只重复_NET值的CASE声明(价格 - 增值税,因为它与增值税的CASE相同,因此会产生错误 .
我想做点什么
SUM(_order_total-_VAT) AS _NET
但是* _order_total 和 _VAT *不会被识别为可以从中计算的列 .
FINAL EDIT 再次感谢Gordon,我的最终查询看起来像这样,我只是添加它以防其他人可能会发现它有用 . 此查询适用于Wordpress导出和报告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
1 回答
您只获得一行的原因是因为
sum()
函数使其成为聚合查询 . 但是,你没有group by
. 我认为postid的分组修正了,以及对select
的一些其他调整:这种类型的数据很难习惯 . 单个记录的所有数据都分为多行 .
要根据位置更改乘数,请使用
where
子句 . 这是一个例子: