我可以优化这个SQL查询吗?

我是否遗漏了一些重要的内容,或者您是否可以对单个聚合查询执行此操作?

SELECT u.UserID,
    SUM(CASE WHEN Success = 1 THEN 1 ELSE 0 END) AS Successful,
    SUM(CASE WHEN Success = 0 THEN 1 ELSE 0 END) AS Unsuccessful,
    MAX(LoginDate) AS LastLogin
FROM Users u
INNER JOIN Logins l on u.UserID = l.UserID
GROUP BY u.UserID

我会尝试这样的方法:

declare @Users Table (UserID int)
declare @Logins Table (LoginID int, UserID int, LoginDate DateTime, Success Bit)

Insert into @Users
select 1 union select 2

insert into @Logins
select 1, 1, '2010-10-13 6:00:00', 1
union
select 2, 1, '2010-10-13 7:00:00', 0
union
select 3, 1, '2010-10-13 8:00:00', 1
union
select 4, 2, '2010-10-13 6:00:00', 0
union
select 5, 2, '2010-10-13 7:00:00', 1
union
select 6, 2, '2010-10-13 9:00:00', 1
union
select 7, 2, '2010-10-13 10:00:00', 1

Select    UserID,
          [1] As Successful,
          [0] As Unsuccessful,
          LoginDate As LoginDate
From (
    SELECT
        u.UserID, 
        l.LoginID,
        l.Success,
        Max(LoginDate) Over (Partition By u.UserID) As LoginDate
    FROM @Users u 
        INNER JOIN @Logins l ON u.UserID = l.UserID
) Data Pivot (
    Count(LoginID) For Success In (
        [0], [1]
    )
) Result

如果在您的情况下更有效,您可以尝试:

select
  u.UserID,   
  sum(case when l.Success = 1 then 1 else 0 end) as Successful,
  sum(case when l.Success = 0 then 1 else 0 end) as Unsuccessful,
  max(LoginDate) as LastLogin
from 
  Users u
  inner join Logins l on l.UserID = u.UserID
group by
  u.UserId