php - 将行连接为税列

Question

我在 mysql 数据库中有两个表。一种是税率，其中创建的税字段如 5% 、 10% (税表中最多 5 行)。另一个表是 sales_item，其中包含带有tax_rate_id字段(与税号相关)的产品。现在最后一个表是 sales，其中包含产品的 bill_id。

销售表:

+--------+------+---------------+-----------+-------
| id     |Name  | Service tax   | Table tax | Total|
+--------+------+---------------+-----------+-------
|     42 |   A  |    10         |    2      |  50  |
|     41 |   B  |    20         |    3      |  60  |
+--------+------+----------------+----------+------+

税表:

+--------+----------+-----------+
| id     | name     | itemvalue |
+--------+----------+-----------+
|      1 | VAT A%   |        10 |
|      2 | VAT B%   |         3 |
|      3 | VAT C%   |         9 |
|      4 | VAT D%   |        40 |
|      5 | VAT E%   |        40 |
+--------+----------+-----------+

sales_item 表:

+--------+---------+------+------ -+
| id     | saleid  | Price| tax_id | 
+--------+---------+---------------+
|      1 |   42    | 1    |  1     |
|      2 |   42    | 2    |  2     |
+--------+---------+------+--------+

我想要这样的输出......输出:

+--------+--------------------+----------+-----+----+--------------+
| id     |No of prdct A% tax  | B% Tax   | C%  | D% | E% | Serv Tax|
+--------+----------------------+--------+----------+----+----------
|     42 |       1            |    1     |  0  | 0  | 0  |  10     |
+--------+------+-------------+----------+-----+-------------------+

那么如何在 mysql 查询中显示这种类型的输出呢？请找到我的数据库架构并执行必要的帮助:pastebin

Answer 1

我已经这样做了这个查询..

SELECT  s.reference_no as rno , a.id, a.product_name, a.sale_id, s.id as sale,
        count(CASE WHEN b.id = 1 THEN b.name ELSE NULL END) as tax1,
        Count(CASE WHEN b.id = 2 THEN b.name ELSE NULL END) tax2,
        Count(CASE WHEN b.id = 3 THEN b.name ELSE NULL END) tax3,
        Count(CASE WHEN b.id = 4 THEN b.name ELSE NULL END) tax4,
        Count(CASE WHEN b.id = 5 THEN b.name ELSE NULL END) tax5,

       sum(CASE WHEN b.id = 1 THEN a.val_tax ELSE NULL END) sum1,
       sum(CASE WHEN b.id = 2 THEN a.val_tax ELSE NULL END) sum2,
       sum(CASE WHEN b.id = 3 THEN a.val_tax ELSE NULL END) sum3,
       sum(CASE WHEN b.id = 4 THEN a.val_tax ELSE NULL END) sum4,
       sum(CASE WHEN b.id = 5 THEN a.val_tax ELSE NULL END) sum5

FROM    sale_items a
        INNER JOIN tax_rates b
            ON a.tax_rate_id = b.id
         JOIN sales s ON s.id = a.sale_id   
GROUP   BY s.id
order BY sale desc