我有三张桌子。
里面的值是这样的。
*tax_master*
tax_id tax_name tax_value
--------------------------
1 Vat 5
2 LBT 8
*item_master*
item_id Prise
---------------
1 30
2 100
*item_tax*
item_tax_id item_id tax_id
------------------------------
1 1 1
2 2 2
3 1 2
现在我想要这样的输出。
item_id prise VAT LBT Total_prise
---------------------------------------
1 30 1.5 2.4 33.9
2 100 - 8 108
增值税值的计算方式如下5/30*100 like 5% on 30=1.5
select item_id, price,
(min(case when tax_name = 'VAT' then tax end)) vat,
(min(case when tax_name = 'LBT' then tax end)) lbt,
coalesce(min(case when tax_name = 'VAT' then tax end),0) +
coalesce(min(case when tax_name = 'LBT' then tax end),0) +
price total
from
(select a.item_id item_id,
c.tax_name tax_name,
(c.tax_value * b.price / 100) tax,
b.price price
from item_tax a inner join item_master b on a.item_id = b.item_id
inner join tax_master c on a.tax_id = c.tax_id) as calc
group by item_id, price;
Demo here.
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
