INSERT INTO userrole ( Id , Name , Description , IsEnabled , Created , CreatedBy , Updated , UpdatedBy ) VALUES (1, 'Role_1', '', 1, '2020-04-14 18:30:00', 'Admin', NULL, NULL), (2, 'Role_2', 'Description', 1, '2020-04-15 18:30:00', 'ADMIN', '2020-04-16 18:30:00', 'John Smith'), (3, 'Role_3', 'Description', 0, '2020-04-15 18:30:00', 'John SMITH', '2020-04-16 18:30:00', 'Ben SMITH'), (4, 'Role_4', 'Description', 1, '2020-04-18 18:30:00', 'bEn SmiTh', '2020-04-20 18:30:00', 'BEN SMITH');
SELECT UPPER(CreatedBy) AS UserName, COUNT(UPPER(CreatedBy)) AS NoOfCreatedRoles, SUM(CASE WHEN IsEnabled = 1 THEN 1 ELSE - 1 END) AS NoOfCreatedAndEnabledRoles,

CASE
    WHEN UpdatedBy IS NOT NULL THEN COUNT(UPPER(UpdatedBy)) ELSE -1
END AS NoOfUpdatedRoles

FROM UserRole GROUP BY CreatedBy ORDER BY CreatedBy desc

试试这个：

SELECT Result.UserName, Result.NoOfCreatedRoles, Result.NoOfCreatedAndEnabledRoles, Result.NoOfUpdatedRoles
FROM (
         Select UPPER(TRIM(CreatedBy))                           AS 'UserName',
                COUNT(UPPER(CreatedBy))                          AS NoOfCreatedRoles,
                SUM(CASE WHEN IsEnabled = 1 THEN 1 ELSE - 1 END) AS NoOfCreatedAndEnabledRoles,
                (SELECT CASE when COUNT(*) = 0 THEN -1 ELSE Count(*) END
                 FROM UserRole as URQ
                 WHERE URQ.UpdatedBy = UR.CreatedBy)             AS NoOfUpdatedRoles
         FROM UserRole UR
         GROUP BY CreatedBy
         ORDER BY UserName
     ) as Result
ORDER BY NoOfCreatedRoles