计算SQL Server查询中的唯一行

I'm having some trouble figuring out a SQL Server query. The relational model is quite old (it seems) and is not very optimal, to say the least.

我在查找SQL Server查询时遇到了一些麻烦。关系模型很老（似乎）并且至少可以说并不是非常优化。

My query looks like this:

我的查询如下所示：

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'

This query gives a result of loads of duplicated records, i.e.:

此查询给出了重复记录的加载结果，即：

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

(I omitted lots of records here)

（我在这里省略了很多记录）

My question is: is there any way to modify the above query to group the duplicated records and make a requestCount column which holds the number of duplicates? Like this:

我的问题是：有没有办法修改上面的查询来对重复的记录进行分组，并创建一个包含重复数量的requestCount列？喜欢这个：

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

? :-)

？ :-)

Thanks in advance!

提前致谢！

3 个解决方案

#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

#2

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

#3

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