如何在SQL中仅显示无法登录的用户并消除其余用户

Question

我有一个包含以下列和数据的表，并且我正在尝试编写一个查询，以便只在用户尚未登录时显示：

SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'password reset' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'logged in' as verb
union all
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all                   
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'password reset' as verb
union all                   
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'logged in' as verb
union all
SELECT 'Kevin' as First_name, 'Owens' as Last_Name, getdate() as timestamp, 'logged in' as verb
union all
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all                   
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all                   
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all                   
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'password reset' as verb
union all
SELECT 'Roman' as First_name, 'Reigns' as Last_Name, getdate() as timestamp, 'password reset' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'tried log in' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'password reset' as verb
union all
SELECT 'Seth' as First_name, 'Rollins' as Last_Name, getdate() as timestamp, 'tried log in' as verb

这为我提供了以下输出：

First_name  Last_Name   timestamp              verb
Seth        Rollins 2017-02-20 15:28:55.660 tried log in
Seth        Rollins 2017-02-20 15:28:55.660 tried log in
Seth        Rollins 2017-02-20 15:28:55.660 password reset
Seth        Rollins 2017-02-20 15:28:55.660 logged in
Kevin       Owens   2017-02-19 15:28:55.660 tried log in
Kevin       Owens   2017-02-19 15:28:55.660 tried log in
Kevin       Owens   2017-02-19 15:28:55.660 tried log in
Kevin       Owens   2017-02-20 15:28:55.660 password reset
Kevin       Owens   2017-02-20 15:28:55.660 logged in
Kevin       Owens   2017-02-21 15:28:55.660 logged in
Roman       Reigns  2017-02-20 15:28:55.660 tried log in
Roman       Reigns  2017-02-20 15:28:55.660 tried log in
Roman       Reigns  2017-02-20 15:28:55.660 tried log in
Roman       Reigns  2017-02-20 15:28:55.660 tried log in
Roman       Reigns  2017-02-20 15:28:55.660 password reset
Roman       Reigns  2017-02-20 15:28:55.660 password reset
Seth        Rollins 2017-02-21 15:28:55.660 tried log in
Seth        Rollins 2017-02-21 15:28:55.660 tried log in
Seth        Rollins 2017-02-21 15:28:55.660 tried log in

我目前正在尝试编写一个查询，以便它只显示罗马王朝的计数，因为他还没有成功登录，所以它将显示他尝试登录的次数以及密码重置。不应显示Seth Rollins的第一组结果，但应显示2017年2月21日的结果，因为他尚未成功登录。

我目前正在尝试找出编写此查询的最佳方法。

我的预期结果如下：

First_Name, Last_Name, Count of TriedLogIn, Count of PasswordResets
Roman        Reigns            4                        2
Seth         Rollins           3                       null

之所以出现赛斯·罗林斯的名字，是因为在20/02成功登录后，他从那一天(21/02)起就没有成功登录过。成功登录后，计数将重置回0。

Answer 1

对于该特定用户，使用case表达式进行条件计数：

select count(case when verb = 'tried log in' then 1 end) as loginfailures,
       count(case when verb = 'password reset' then 1 end) as passwordresets
from tablename
where First_name = 'Roman' and Last_name = 'Reigns'

如果您想要相同的信息，但对于所有用户，请执行GROUP BY

select First_name, Last_name,
       count(case when verb = 'tried log in' then 1 end) as loginfailures,
       count(case when verb = 'password reset' then 1 end) as passwordresets
from tablename
group by First_name, Last_name

Answer 2

使用not exists

select *
from users u1
where verb = 'tried log in'
and not exists
(
select 1
from users u2
where u1.firstname = u2.firstname 
and u1.lastname = u2.lastname
and u2.verb = 'logged in'
)