In the IDC charging system, the current balance of students must be reflected in several aspects. The balance involves the consumption amount, registration amount, and the amount at the time of recharge of the same card number, all three amounts are placed in three places. If they are obtained separately, three methods are required at Layer B. If you place these three amounts in the same place, you need to create one more table. How can I query all three amounts at a time without adding a table?
In fact, this involves writing SQL statements.
Select sum (table 1. field), sum (table 2. field), sum (table 3. field), from table 1 cross join Table 2 cross join Table 3 where (table 1. field = table 2. field and table 2. field = table 3. field and table 1. field = query parameters)
For example:
Select sum (outlineinfo. consumecash), sum (rechargeinfo. addmoney), sum (studentinfo. Cash) from outlineinfo cross join rechargeinfoCross joinStudentinfo where (studentinfo. cardno = rechargeinfo. cardno
And rechargeinfo. cardno = outlineinfo. cardno and studentinfo. cardno = @ cardno)
In this way, the parameters to be searched in multiple tables can be queried at a time.
Summary:
I had to think about this small problem for a long time. I found a lot of information to get it out. Why? We have learned about SQL a long time ago, but what we learned was nothing more than an outline. When I heard it, I only knew about it and what it was possible. The overall impression on SQL was just about adding, deleting, modifying, and querying SQL, but I don't know how to do it, so I need to learn more about SQL in the future.
In addition, the key points of thinking logic and knowledge searchingIt is also crucial. Just like this problem, I started to think of a new way to create a table for money (including the three fields ).CodeYou must also modify the UML diagram. after doing so, the more troublesome I am, the more simple the method is. Using a view and view to create a view can't achieve what I want. I can find another method, search books, and search the Internet. By chance, I can see an SQL statement. After repeated experiments, this is the purpose of modification.From the very beginning, it would be too difficult to create another table, so I would not spend any time writing this part of the code.
But how can we achieve the current success if there is no start failure? No matter what, I have learned ....