gpt4 book ai didi

mysql - 多次引用一个外键

转载 作者:搜寻专家 更新时间:2023-10-30 20:34:15 25 4
gpt4 key购买 nike

在我的项目中,有收入也有支出。我有很多生意和 Assets 。每个企业和 Assets 都有收入和支出。除此之外,我还有一些一般收入,如工资、奖金等。所以最后我做了 6 个表,

1. business, 2. assets, 3. catagory, 4. income, 5. expense, 6. accounts

+----------+---------+ +--------+---------+ +----------+---------+ +----------+---------+
| Business | Name | | Assets | Name | | catagory | Name | | account | Name |
+----------+---------+ +--------+---------+ +----------+---------+ +----------+---------+
| 1 |Business1| | 1 | Land1 | | 1 | profit | | 1 | Bank1 |
| 2 |Business2| | 2 | Land2 | | 2 | salary | | 2 | Bank2 |
| 3 |Business3| | 3 | Land3 | | 3 | Rent | | 3 | Cash |
+----------+---------+ +--------+---------+ +----------+---------+ +----------+---------+

金融交易可能发生在企业、 Assets 或一般交易中,每个交易都有类别,或者可能将余额从一个账户转移到另一个账户。如下图

+--------+---------+---------+---------+---------+                             
| Income | From | Catagory| Account | Amount |
+--------+---------+---------+---------+---------+
| 1 |Business1|Profit |Bank1 | 1000 |
| 2 |Land1 |Rent |Bank2 | 500 |
| 3 |General |Salary |Cash | 700 |
| 4 |Transfer |Null |Bank2 | 500 |
+--------+---------+---------+---------+---------+

等等费用

+--------+---------+---------+---------+---------+                             
| Expense| From | Catagory| Account | Amount |
+--------+---------+---------+---------+---------+
| 1 |Transfer | Null | Bank1 | 500 |
| 2 |Land1 | Mainta..| Bank2 | 200 |
| 3 |General | Food | Cash | 700 |
| 4 |Assets | Invest | Bank1 | 1000 |
+--------+---------+---------+---------+---------+

这个设计有一些严重的问题,插入后我无法从收入或支出表中跟踪“来自”列。这个领域变得模棱两可。

我必须准备损益表、 Assets 负债表、账户摘要、业务和 Assets 明智报告,但我不知道如何处理这个数据库或如何修复这些表格。

请提供任何建议或想法来修复这些表结构以准备我的损益表和 Assets 负债表。

最佳答案

选项 1(简单方法):创建一个From字段以区分 Assets 和业务

Option 1

选项 2(正确方法):创建一个 From table 来区分 Assets 和业务

Option 2

选项 2 的最终查询:

SELECT 
IncomeID,
IF(
(SELECT COUNT(assets.assetsID) FROM assets WHERE Income.FromId = assets.FromId) > 0,
(SELECT assets.name FROM assets WHERE Income.FromId = assets.FromId),
(SELECT Business.name FROM Business WHERE Income.FromId = Business.FromId),
) AS From,
Catagory.name,
Account.name,
Income.amount
FROM
Income, Catagory, Account
WHERE
Income.CatagoryId = Catagory.CatagoryId
AND
Account.AccountId = Account.AccountId
ORDER BY
IncomeID, Income.amount;

关于mysql - 多次引用一个外键,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49165296/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com