如何在mysql表中创建行,如何通过在MySQL的另一个表中创建行来选择表中的列

I have three tables.

tax_master

item_master

item_tax

The values in it are like this.

*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

Now i want output like this.

item_id prise VAT LBT Total_prise

---------------------------------------

1 30 1.5 2.4 33.9

2 100 - 8 108

VAT value is calculated like 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.