如何通过在MySQL的另一个表中创建行来选择表中的列 [英] How to select column in table by creating row in another table in MySQL

问题描述

我有三个桌子.

• tax_master
• item_master
• item_tax

其中的值是这样的.

*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

增值税值的计算方式类似于5/30*100，例如30=1.5上的5%

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;

演示此处.

这篇关于如何通过在MySQL的另一个表中创建行来选择表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！