Look at this requirement: After a website has been running for some time, you want to see when the user last logged in.
Here is the table structure:
--User TableCREATE TABLE [dbo].[T_userinfo]([ID] [int] IDENTITY(1,1) not NULL,--User ID[name] [nvarchar]( -)NULL --User name) on [PRIMARY]
-- Insert test data to User table INSERT into [dbo]. [t_userinfo] ([name]VALUES (' Zhang San '), (' John Doe '), (' Harry ')
--Login ScheduleCREATE TABLE [dbo].[t_sign]([ID] [int] IDENTITY(1,1) not NULL,[UserId] [int] NULL,[Signtime] [datetime] NULL --Logon Hours) on [PRIMARY]--inserting test data into a login scheduleINSERT into [dbo].[t_sign]([UserId],[Signtime])VALUES(1,'2017-03-01 10:55:32'),(1,'2017-02-01 10:55:32'),(1,'2017-01-01 10:55:32'),(2,'2016-03-02 10:55:32'),(2,'2017-02-01 10:55:32'),(2,'2015-01-01 10:55:32'),(3,'2017-03-11 10:55:32'),(3,'2017-02-21 10:55:32'),(3,'2015-01-01 10:55:32')
---View when the user last logged inSelectU.id,u.name,s.signtime Lastsigntime fromT_userinfo u Left Join [t_sign]S onU.id=S.useridwhereS.signtime=(Select Max(Signtime) fromT_signwhereUserid=u.id);Select * fromT_sign--this sentence to do reference
------------------------------------I'm a split line-----------------------------------------------------
This is also true in Oracle. Reference here https://zhidao.baidu.com/question/513758407.html
SQL Server one-to-many queries