mysql - 使用 MySQL 列出来自 3 个不同表的项目

Question

我有以下表格结构并试图从这些表格中生成报告:

___计费数据

|--------|------------|---------|--------------|------------|
| BIL_Id | BIL_Date   |BIL_Rate | BIL_Quantity | BIL_Status |
|--------|------------|---------|--------------|------------|
|      1 | 2018-03-01 |     105 |            2 | notcharged |
|      2 | 2018-03-02 |     115 |            2 |    charged |
|      3 | 2018-03-03 |     105 |            1 | notcharged |
|      4 | 2018-03-04 |     105 |            1 | notcharged |
|      5 | 2018-03-05 |     105 |            3 | notcharged |
|--------|------------|---------|--------------|------------|

___营业税

|--------|--------------|------------|
| STX_Id | STX_TaxeName | STX_Amount |
|--------|--------------|------------|
|      8 |        Tax 1 |      5.000 |
|      9 |        Tax 2 |     15.000 |
|--------|--------------|------------|

STX_Amount 是一个百分比。

___适用税

|-----------|-----------|
| ATX_BILId | ATX_STXId |
|-----------|-----------|
|         1 |         8 |
|         1 |         9 |
|         2 |         9 |
|         3 |         9 |
|         4 |         9 |
|         5 |         9 |
|-----------|-----------|

• ATX_BILId 是与 ___BillableDatas 链接的项目 ID。
• ATX_STXId 是与 ___SalesTaxes 关联的税号。

---

所以像这样:

|------------|--------------|------------|------------|-----------|
| BIL_Date   | STX_TaxeName | BIL_Rate   | STX_Amount | STX_Total |
|------------|--------------|------------|------------|-----------|
| 2018-03-01 |        Tax 1 |    210.00  |      5.000 |     10.50 |
| 2018-03-01 |        Tax 2 |    210.00  |     15.000 |     31.50 |
| 2018-03-03 |        Tax 2 |    105.00  |     15.000 |     15.75 |
| 2018-03-04 |        Tax 2 |    105.00  |     15.000 |     15.75 |
| 2018-03-05 |        Tax 2 |    315.00  |     15.000 |     47.25 |
|------------|--------------|------------|------------|-----------|

我不想要状态为:charged

---

总数说明:

• STX_TaxeName 是来自 ___SalesTaxes 的相关税的名称。
• BIL_Rate 是来自 ___BillableDatas 的 BIL_Rate * BIL_Quantity。
• STX_Amount 是来自 ___SalesTaxes 的税率。
• STX_Total 是根据 BIL_Rate 计算的百分比。 (BIL_Rate*STX_Amount/100)。

---

我最后一次尝试是这个:

SELECT BIL_Status
    , SUM(BIL_Rate*BIL_Quantity) BIL_Rate
    , IFNULL(SUM((BIL_Rate*BIL_Quantity)+(BIL_Rate*BIL_Quantity*total_sales_tax/100)), SUM(BIL_Rate*BIL_Quantity)) STX_Total 
    FROM 
    ( SELECT b.*
            , SUM(t.STX_Amount) total_sales_tax
            , STX_TaxeName
            , STX_Amount
        FROM ___BillableDatas b 
        LEFT JOIN ___ApplicableTaxes bt 
            ON bt.ATX_BILId = b.BIL_Id 
        LEFT JOIN ___SalesTaxes t 
            ON t.STX_Id = bt.ATX_STXId 
        GROUP  
            BY ATX_BILId
     ) x
WHERE BIL_Status = 'notcharged'
ORDER 
    BY BIL_Date DESC    
GROUP 
    BY BIL_Status

如果需要，请参阅此 SQLFiddle 以帮助您:
http://sqlfiddle.com/#!9/2bb244/11

谢谢。

Answer 1

你不能只用一个简单的 JOIN 来完成吗？

SELECT BIL_Date, STX_TaxeName, BIL_Rate * BIL_Quantity AS NewRate, STX_Amount
  , BIL_Rate * BIL_Quantity * STX_Amount / 100 AS Total
FROM ___BillableDatas b 
JOIN ___ApplicableTaxes bt ON bt.ATX_BILId = b.BIL_Id 
JOIN ___SalesTaxes t ON t.STX_Id = bt.ATX_STXId 
WHERE BIL_Status = 'notcharged' 
ORDER BY BIL_Date, STX_TaxeName

使用来自您的 sqlfiddle 的 GROUP BY 更新查询:

SELECT 
    BIL_Date
    , STX_Id
    , STX_TaxeName
    , SUM(BIL_Rate * BIL_Quantity) AS NewRate
    , STX_Amount
    , SUM(BIL_Rate * BIL_Quantity * STX_Amount / 100) AS Total
FROM ___BillableDatas b 
    JOIN ___ApplicableTaxes bt ON bt.ATX_BILId = b.BIL_Id 
    JOIN ___SalesTaxes t ON t.STX_Id = bt.ATX_STXId 
WHERE BIL_Status != 'cancelled' 
AND BIL_Date between "2018-03-01" AND "2018-03-31"
GROUP BY BIL_Date, STX_Id, STX_TaxeName, STX_Amount
ORDER BY BIL_Date, STX_TaxeName