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
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
764
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
