A SQL application problem encountered at work

Log in Log table

CREATE TABLE [dbo]. [Loginlog]
(
[Seq] [INT] Not NULL IDENTITY (1, 1),--seq
[UserId] [varchar] (COLLATE) Chinese_prc_ci_as NULL,--User name
[Logintime] [DateTime] NULL,--Logon time
[SUCCESSFLG] [Bit] NULL,--whether the login was successful

) on [PRIMARY]
GO

Requirement: Returns whether the user can continue to log in

Login rules: If the user logs in three times within 1 hours of error, the lock cannot be logged in for 1 days

Implementing SQL

Select Case is COUNT (1) = 3 then 1 ELSE 0 END lockflgfrom (select TOP 3 l.* from Loginlog L with (nolock) INNER JOIN (Sele CT TOP 1 seq,successflg,logintime,userid from dbo. Loginlog with (nolock) WHERE userid= ' sdf333 ' and Logintime >= DATEADD (Day,-1,getdate ()) ORDER by Seq DESC) Lastlog on l.se Q  <= lastlog.seq and L.userid = Lastlog.useridwhere  DATEDIFF (HOUR, L.logintime,lastlog.logintime) < 1ORDER by L.SEQ  desc) as Twhere T.SUCCESSFLG = 0

A newcomer to the company is very interested in SQL. Let him try. Given SQL:

Select  COUNT (1) lockflg from (Select Case is COUNT (*) =3 then 1 ELSE 0 END as Near3,case when DATEDIFF (Hour,min (t.Log InTime), MAX (t.logintime)) <1 then 1 else 0 END as Inonehour,case if SUM (CAST (t.successflg as INT)) =0 then 1 else 0 EN D as Wrongtimes,case when DATEDIFF (Hour,max (T.logintime), GETDATE ()) <24 then 1 ELSE 0 END as Inonedayfrom (SELECT TOP 3 * FROM dbo. Loginlogwhere userid= ' sdf333 ' ORDER by Logintime DESC) t) Ttwhere Near3=1 and Inonehour=1 and Wrongtimes=1 and Inoneday=1

This classmate's idea is more simple and direct, feel I am already old ~ ~ ~

A SQL application problem encountered at work