About the SQL Microsoft face question on the forum. My method of solution:-)

Solution | microsoft
Problem:

Each of the 100 accounts is 100$ and a new record is added to the account one day to record the balance. 100 days, please output the balance information of all accounts every day


The difficulty with this problem is that each user may have more than one record in one day, or not a single record (excluding the first day).

The recordset returned is a record set of 100 days * 100 users

Here are my thoughts:

1. Create a table and insert test data: We request username from 1-100
CREATE TABLE [dbo]. [TABLE2] (
[Username] [varchar] Not NULL,--User name
[Outdate] [DateTime] Not NULL,--date
[Cash] [Float] Not NULL-balance
) on [PRIMARY

DECLARE @i int
Set @i=1
While @i<=100
Begin
Insert Table2 VALUES (CONVERT (varchar, @i), ' 2001-10-1 ', 100)
Insert Table2 VALUES (CONVERT (varchar, @i), ' 2001-11-1 ', 50)
Set @i=@i+1
End
Insert Table2 VALUES (CONVERT (varchar, @i), ' 2001-10-1 ', 90)

SELECT * FROM Table2 ORDER by Outdate,convert (Int,username)

2. Combined Query statement:
A. We must return a set of records starting from the first day to 100 days:
For example: 2001-10-1 (this date is arbitrary) to 2002-1-8
Since the first day is any day, we need the following SQL statement:
Select Top DateAdd (D,convert (int,username) -1,min (outdate)) as Outdate
From table2
GROUP BY username
ORDER by convert (Int,username)
The secret here is:
Convert (Int,username)-1 (remember we specified username from 1-100:-))
Group by Username,min (outdate): You may have multiple records per user on the first day.
The result returned:
Outdate
------------------------------------------------------
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000

B. Returns a record set of all user names:
Select DISTINCT username from table2
return Result:
Username
--------------------------------------------------
1
10
100
......
99

C. Returns a Cartesian collection of 100-day recordsets and 100 user recordsets:
SELECT * FROM
(
Select Top DateAdd (D,convert (int,username) -1,min (outdate)) as Outdate
From table2
GROUP BY username
ORDER by convert (Int,username)
) as A
CROSS Join
(
Select DISTINCT username from table2
) as B
Order by Outdate,convert (Int,username)
Return result 100*100 record:
Outdate username
2001-10-01 00:00:00.000 1
......
2002-01-08 00:00:00.000 100

D. Returns the records of all current users in the database:
Select Outdate,username,min (Cash) as cash from table2
GROUP BY Outdate,username

Order by Outdate,convert (Int,username)
Return record:
Outdate username Cash
2001-10-01 00:00:00.000 1 90
......
2002-01-08 00:00:00.000 100 50

E. Make a LEFT join of the records returned in Cartesian sets and D in C:
Select C.outdate,c.username,
D.cash
From
(
SELECT * FROM
(
Select Top DateAdd (D,convert (int,username) -1,min (outdate)) as Outdate
From table2
GROUP BY username
ORDER by convert (Int,username)
) as A
CROSS Join
(
Select DISTINCT username from table2
) as B
) as C
Left Join
(
Select Outdate,username,min (Cash) as cash from table2
GROUP BY Outdate,username
) as D
On (C.username=d.username and DateDiff (d,c.outdate,d.outdate) =0)

Order by C.outdate,convert (Int,c.username)
Note: If the user does not have a record for the day, the cash field returns NULL, otherwise cash returns the balance of the day for each user
Outdate username Cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2001-10-02 00:00:00.000 1 90
2001-10-02 00:00:00.000 2 NULL <--Note here
......

2002-01-08 00:00:00.000 100 50

F. Ok, now all we have to do is, if cash is null, we want to return the first user balance that is less than the current record date (since we use ORDER by cash, so we can return top 1 records, use min should also), this balance is the current balance:
Case IsNull (d.cash,0)
When 0 Then
(
Select top 1 cash from table2 where table2.username=c.username
and DateDiff (d,c.outdate,table2.outdate) <0
ORDER BY Table2.cash
)
else D.cash
End as Cash

G. The complete statement of the final combination is
Select C.outdate,c.username,
Case IsNull (d.cash,0)
When 0 Then
(
Select top 1 cash from table2 where table2.username=c.username
and DateDiff (d,c.outdate,table2.outdate) <0
ORDER BY Table2.cash
)
else D.cash
End as Cash
From
(
SELECT * FROM
(
Select Top DateAdd (D,convert (int,username) -1,min (outdate)) as Outdate
From table2
GROUP BY username
ORDER by convert (Int,username)
) as A
CROSS Join
(
Select DISTINCT username from table2
) as B
) as C
Left Join
(
Select Outdate,username,min (Cash) as cash from table2
GROUP BY Outdate,username
) as D
On (C.username=d.username and DateDiff (d,c.outdate,d.outdate) =0)

Order by C.outdate,convert (Int,c.username)

return Result:
Outdate username Cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2002-01-08 00:00:00.000 100 50

Let's see if there are any bugs, if you find bugs or you have a better way, you may email me: hydnoahark@netease.com ^-^