用于计算完成任务的唯一用户数的 SQL 语法

Question

我有以下代码，它向我显示了哪些用户完成了工单，并列出了每个用户以及他们关闭工单的日期。 即

Paul
Matt
Matt
Bob
Matt
Paul
Matt
Matt

目前，我自己手动计算每个用户以查看他们当天的总数。

编辑：将输出更改为列而不是行：

我一直在尝试做的是让SQL Server为我执行此操作，即最终结果如下所示：

Paul  |  2
Matt  |  5
Bob   |  1

我目前正在使用的代码是，如果有人可以帮助我更改它，以便我可以让它输出类似于上面的东西，我会很棒？

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today

SELECT  (select Username from Membership where UserId =  Ticket.CompletedBy) as TicketStatusChangedBy
FROM         Ticket INNER JOIN
                      TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID INNER JOIN
                      Membership ON Ticket.CheckedInBy = Membership.UserId
WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate --(GETDATE() - 1)
and Ticket.ClosedDate <= @EndDate --(GETDATE()-0)
ORDER BY Ticket.CompletedBy ASC, Ticket.ClosedDate ASC

感谢您的帮助和时间。

Answer 1

不确定您是否特别想要这种格式，但是如果您将其放在行中，则作为查询要简单得多：

SELECT Names, COUNT(Names)
FROM Table1
GROUP BY Names

SQL 小提琴

Answer 2

Declare @t table(names varchar(100))
DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today
insert into @t(names)
SELECT  (select Username from Membership where UserId =  Ticket.CompletedBy) as TicketStatusChangedBy
FROM         
Ticket INNER JOIN TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID INNER JOIN
         Membership ON Ticket.CheckedInBy = Membership.UserId
WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate 
--(GETDATE() - 1)
and Ticket.ClosedDate <= @EndDate --(GETDATE()-0)
ORDER BY Ticket.CompletedBy ASC, Ticket.ClosedDate ASC
Select 
sum(case when names='Paul' then 1 else 0 end) as Paul,
sum(case when names='Matt' then 1 else 0 end) as Matt,
.
.
.
from @t

Answer 3

CREATE TABLE  SomeTable 
(Name NVARCHAR(20))
GO
INSERT INTO SomeTable 
VALUES ('Mark'), ('Mark'),('Mark'),('SAM'),('SAM'),('Josh')
GO
;WITH CTE
AS
(
    SELECT NAME, COUNT(*) AS Total FROM SomeTable
    GROUP BY Name
)
SELECT * 
FROM CTE
        PIVOT (
                MAX(Total)
                FOR NAME 
                IN ([Mark], [SAM], [Josh])      
               )l

结果集

Mark SAM  Josh
3    2    1

显然，如果表中有很多值，则需要使用动态 Sql，首先构建所有不同值/名称的逗号定义列表，然后将上述查询的"IN"坐标系中的名称列表传递给动态 SQL。
使用动态 SQL

DECLARE @NameList NVARCHAR(MAX) = ''
DECLARE @Sql NVARCHAR(MAX)
SELECT @NameList =   @NameList + ',['  + Name + ']' 
                    FROM (SELECT DISTINCT NAME FROM SomeTable)t
SET @NameList =    STUFF(@NameList, 1, 1, '')
PRINT @NameList      --<---- for checking purpose to see if list is as expected
SET @Sql = ';WITH CTE
AS
(
    SELECT NAME, COUNT(*) AS Total FROM SomeTable
    GROUP BY Name
)
SELECT * 
FROM CTE
        PIVOT (
                MAX(Total)
                FOR NAME 
                IN ('+ @NameList + ')       
               )l'
EXECUTE sp_executesql @Sql

结果集

Mark SAM  Josh
3    2    1