problem:
One hundred accounts have 100 $, and a new record is added as a certain day, and the balance is recorded. After a hundred days, please output the balance information of all accounts every day.
The difficulty of this problem is that each user may have multiple records in a certain day, or there may be a record that has not been recorded (excluding the first day) The recordset returned by the return is a 100-day * 100 users' record set below is my idea. : 1. Create a table and insert the test data: We ask UserName from 1-100Create Table [DBO]. [Table2] ([username] [varcha] (50) Not null, - Username [Outdate] [DateTime] not null , - Date [Cash] [Float] Not Null - Balance) ON [PrimaryDeclare @i Intset @ i = 1WHILE @i <= 100 Begin INSERT TABLE2 VALUES (CONVERT (varchar (50), @ i), '2001- 10-1 ', 100) INSERT TABLE2 VALUES (Convert (varchar (50), @ i),' 2001-11-1 ', 50) set @ i = @ i 1 endinsert Table2 Values (control (VARCHAR (50) , @ i), '2001-10-1', 90) Select * from table2 Order by Outdate, Convert (int, username) 2. Combined query statement: a. We must return a one from the first day to 100 days Recipes: such as: 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 100 Dateadd (D, Convert) Username) -1, min (outdate)) as outdatefrom table2group by UserNameOrder by Convert (int, username) The mystery here is: Convert (int, username) -1 (remember we specify the username from 1-100 :-)) Group By UserName, MIN (Outdate): You may have multiple records per user on the first day.
Return to the result: Outdate --------------------------------------------- --------- 2001-10-01 00: 00: 00.000 ......... 2002-01-08 00: 00:00.000B. Return to a recordset of all usernames: SELECT Distinct username from table2 Return Result: UserName ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- 110100 ... 99c. Return a 100-day record set and 100 user record set Descartes collection: SELECT * FROM (D, Convert) ) -1, min (outdate)) as outdatefrom table2group by usernameorder by convert (int, username)) as ACROSS join (select distinct username from table2) as Border by outdate, convert (int, username) returns the result record 100 * 100 : Outdate UserName2001-10-01 00: 00: 00.000 1 ... 2002-01-08 00:00:00.000 100d. Return to all users in the database: SELECT OUTDATE, Username, MIN (Cash ) AS Cash from table2group by Outdate, UsernameOrder by Outdate, Convert (int, username) Return Recology: Outdate UserName Cash2001-10-01 00: 1 90 .... ..2002-01-08 00: 00: 00 50E. Document returned in Cartesque in C and D. rsOin: Select C.Outdate, C.userName, D.cashfrom select top 100 dateadd (d, convert (int, username) -1, min (outdate)) as outdatefrom table2group by usernameorder by convert (int, username)) as ACROSS join (select distinct username from table2) as B) as Cleft join (Select Outdate, Username, Min (Cash) AS Cash from table2group by Outdate, Username) AS DON (C.USERNAME = D.USERNAME AND DATEDIFF (D, C.outdate, D.outdate) = 0) Order by c.outdate , Convert (int, c.username) Note: If there is no record on the day, the Cash field returns NULL.
