使用Mysql从不同的表中选择具有不同名称的值作为信用卡和借记卡并计算余额

I need to get values from different tables with different column names and select it as a common name that I could make a balance calculation. Something like that:

我需要从不同的表中获取具有不同列名的值，并选择它作为我可以进行余额计算的通用名称。像这样的东西：

Table 1 (credit)

表1（信用）

+-----------+--------------+---------------+
| ID_company|  date_table1 |  credit_table1|
+-----------+--------------+---------------+
|         1 |   2017-08-19 |            50 | 
|         1 |   2017-08-19 |           250 |
|         1 |   2017-08-20 |             0 |
+-----------+--------------+---------------+

Table 2 (credit)

表2（学分）

+-----------+--------------+---------------+
| ID_company|  date_table2 |  credit_table2|
+-----------+--------------+---------------+
|         1 |   2017-08-19 |           150 | 
|         1 |   2017-08-19 |            50 |
|         1 |   2017-08-19 |             0 |
+-----------+--------------+---------------+

Table 3 (debit)

表3（借记）

+-----------+--------------+---------------+
| ID_company|  date_table3 |   debit_table3|
+-----------+--------------+---------------+
|         1 |   2017-08-19 |            10 | 
|         1 |   2017-08-19 |            10 |
|         1 |   2017-08-20 |             0 |
+-----------+--------------+---------------+

Table 4 (debit)

表4（借方）

+-----------+--------------+---------------+
| ID_company|  date_table4 |   debit_table4|
+-----------+--------------+---------------+
|         1 |   2017-08-19 |            10 | 
|         1 |   2017-08-19 |            10 |
|         1 |   2017-08-20 |             0 |
+-----------+--------------+---------------+

Result

结果

+-----------+--------------+---------------+--------------+---------+
| ID_company|  date_grouped|   total_debit | total_credit | balance |
+-----------+--------------+---------------+--------------+---------+
|         1 |   2017-08-19 |            20 |          200 |     180 |
|         1 |   2017-08-19 |            20 |          300 |     280 | 
|         1 |   2017-08-20 |             0 |            0 |       0 |
+-----------+--------------+---------------+--------------+---------+

The relation between the tables is the ID_company, but the column names are different and I need to calculate the credit, debit and balance from all tables grouped by date.

表之间的关系是ID_company，但列名不同，我需要计算按日期分组的所有表的贷记，借方和余额。

1 个解决方案

#1

I think this should work, even if there is no entry for a given date in up to three tables.
Balance is per day, not accumulative.

我认为这应该有效，即使在最多三个表中没有给定日期的条目。平衡是每天，而不是累积。

It will not produce your example result, but I think there's an error in your example. Do you really need two rows for ID 1 and the same date?

它不会产生您的示例结果，但我认为您的示例中存在错误。你真的需要两行ID 1和相同的日期？

SELECT 
    ID_company, 
    date_grouped, 
    sum(credit_table1) + sum(credit_table2) total_credits, 
    sum(debit_tabel3) + sum(debit_table4) total_debit, 
    sum(debit_tabel3) + sum(debit_table4)
        - (sum(credit_table1) + sum(credit_table2)) balance
FROM (
    SELECT 
        ID_Company, 
        date_table1 date_grouped, 
        credit_table1,
        0 credit_table2, 
        0 debit_table3, 
        0 debit_table4 
    FROM table1
    UNION
    SELECT 
        ID_Company, 
        date_table2 date_grouped, 
        0 credit_table1, 
        credit_table2,
        0 debit_table3, 
        0 debit_table4 
    FROM table2
    UNION
    SELECT 
        ID_Company, 
        date_table3 date_grouped, 
        0 credit_table1, 
        0 credit_table2, 
        debit_table3,
        0 debit_table4 
    FROM table3
    UNION
    SELECT 
        ID_Company, 
        date_table4 date_grouped, 
        0 credit_table1, 
        0 credit_table2, 
        0 debit_table3,
        debit_table4
    FROM table4  
)
GROUP BY ID_company, date_grouped

#1