sql - 计算 SQL Server 查询中的唯一行

Question

我在计算 SQL Server 查询时遇到了一些问题。关系模型相当陈旧(看起来)并且至少可以说不是最优的。

我的查询是这样的:

SELECT 
    [RequestUsers].requestId, [Requests].name, [Requests].isBooked
FROM 
    [RequestUsers]
JOIN 
    [Requests] ON [RequestUsers].requestId = [Requests].id
WHERE 
    [RequestUsers].dateRequested >= '10-01-2013'
    AND [RequestUsers].dateRequested <= '10-16-2013'

这个查询给出了一个包含大量重复记录的结果，即:

id      name        isBooked
-----------------------------
1393    Request1       0
1393    Request1       0
1393    Request1       0    
1394    Request2       0
1394    Request2       0
1399    Request3       0
1399    Request3       0
1399    Request3       0
1399    Request3       0
1399    Request3       0

(这里省略了很多记录)

我的问题是:是否有任何方法可以修改上述查询以对重复记录进行分组并创建一个包含重复项数量的 requestCount 列？像这样:

id      name        isBooked     requestCount
---------------------------------------------
1393    Request1       0              3
1399    Request2       0              2
1393    Request3       0              5

? :-)

提前致谢!

Answer 1

SELECT [RequestUsers].requestId, 
       [Requests].name, 
       [Requests].isBooked,
       Count(*) AS requestCount
FROM   [RequestUsers]
       JOIN [Requests]
           ON [RequestUsers].requestId = [Requests].id
WHERE  [RequestUsers].dateRequested >= '10-01-2013'
       AND [RequestUsers].dateRequested <= '10-16-2013'
GROUP BY [RequestUsers].requestId, 
       [Requests].name, 
       [Requests].isBooked