Collection table: jzShoukuanBill (primary key ID, date of payment, payment amount, bank account ID)
Payment table: jzFukuan (primary key ID, date of payment, payment amount, bank account ID)
Transfer table: jzZhuanzhang (primary key ID, the date of transfer, transfer out of the bank account, turn into a bank account, transfer amount)
Ask a bank trading water sort by date
The results are as follows:
Transaction number (by running water growth starting from 1), date, account code, name of account, amount before receiving instant balance of payment
CodePudding user response:
/*Bank account table jzBancAcount (primary key ID, code, open an account name, date of initial amount, instant balance)
Collection table: jzShoukuanBill (primary key ID, date of payment, payment amount, bank account ID)
Payment table: jzFukuan (primary key ID, date of payment, payment amount, bank account ID)
Transfer table: jzZhuanzhang (primary key ID, the date of transfer, transfer out of the bank account, turn into a bank account, transfer amount)
Ask a bank trading water sort by date
The results are as follows:
Transaction serial number (in growth starting from 1) water, before the date, account code, name of account, amount of balance of payment immediately
*/
Int the CREATE TABLE # jzBancAcount (ID, code VARCHAR (20), the name of the VARCHAR (20), account opening Date to the Date, initial value of the int, int) instant balance
INSERT INTO # jzBancAcount values (1, '001', 'investment accounts',' 2021-03-01 ', 1000100 0)
INSERT INTO # jzBancAcount values (2, '002', 'construction bank accounts',' 2021-03-01 ', 2000200 0)
CREATE TABLE # jzShoukuanBill (ID int, int bank account ID, Date of payment Date, the payment amount int)
INSERT INTO # jzShoukuanBill values (1, 1, 'the 2021-03-01 9:00:00, 500)
INSERT INTO # jzShoukuanBill values (2, 1, 'the 2021-03-01 10:00:00, 300)
INSERT INTO # jzShoukuanBill values (3, 1, 'the 2021-03-02 11:00:00, 100)
INSERT INTO # jzShoukuanBill values (5, 2, '2021-03-01 9:00:00, 250)
INSERT INTO # jzShoukuanBill values (6, 2, '2021-03-06 9:00:00, 350)
CREATE TABLE # jzFukuan (ID int, int bank account ID, Date of payment Date, the payment amount int)
INSERT INTO # jzFukuan values (1, 1, 'the 2021-03-01 9:10:00, 50)
INSERT INTO # jzFukuan values (2, 1, 'the 2021-03-01 10:10:00, 30)
INSERT INTO # jzFukuan values (3, 1, 'the 2021-03-02 11:10:00, 10)
INSERT INTO # jzFukuan values (5, 2, '2021-03-01 9:10:00, 250)
INSERT INTO # jzFukuan values (6, 2, '2021-03-06 8:00:00, 150)
The CREATE TABLE # jzZhuanzhang (ID int, turn out of the bank account ID int, into the bank account ID int, transfer Date Date, transfer amount int)
INSERT INTO # jzZhuanzhang values (1, 2, 1, 'the 2021-03-01 9:30:00, 250)
INSERT INTO # jzZhuanzhang values (1,1,2, 'the 2021-03-06 8:30:00, 150)
/* and */a SQL statement to get the following results
Serial number date account code name initial amount payment immediately the balance of payment
1 9:00:00 merchant account 1000 001 2021-03-01 500 0 1500
2 9:10:00 merchant account 1500 001 2021-03-01 0 1450
3 9:30:00 merchant account 1450 250 1700 001 2021-03-01
4 10:00:00 merchant account 1700 300 2000 001 2021-03-01
5 10:10:00 merchant account 2000 001 2021-03-01 0 30 1970
6 11:00:00 merchant account 1970 001 2021-03-02 100 0 2070
7 11:10:00 merchant account 2070 001 2021-03-02 10 2060
8 8:30:00 merchant account 2060 001 2021-03-06 150 1910
9 9:00:00 CCB account 2000 002 2021-03-01 250 0 2250
10 9:10:00 CCB account 2250 002 250 2021-03-01 2000
11 9:30:00 CCB account 2000 002 250 2021-03-01 1750
12 8:00:00 CCB account 1750 002 150 2021-03-06 1600
13 8:30:00 CCB account 1600 002 2021-03-06 150 0 1750
13 9:00:00 CCB account 1750 002 2021-03-06 350 0 2100
Drop table # jzBancAcount
Drop table # jzShoukuanBill
Drop table # jzFukuan
Drop table # jzZhuanzhang
CodePudding user response:
The last number is wrong, 14