每个月的SQL Server 2005 GROUP BY和COUNT查询

I have a SQL Server 2005 table named Rentals:

我有一个名为Rentals的SQL Server 2005表:

RentalID
Book
Date

I want to return, using a query, for each book, how many rentals were in each month for a given year.

我想使用查询为每本书返回一年中每月租金的数量。

The results should look something like this:

结果应如下所示:

+--------------------------------+-----+-----+-----+  
|              Book              | Jan | Feb | Mar |
+--------------------------------+-----+-----+-----+  
| Isaac Asimov - Foundation      |   2 |   5 |   3 |  
| H.G. Wells - War of the Worlds |   4 |   3 |   1 |  
| Frank Herbert - Dune           |   7 |   4 |   6 |
+--------------------------------+-----+-----+-----+

My query so far:

我的查询到目前为止:

SELECT
Book, 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=1 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=2 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=3 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=4 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=5 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=6 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=7 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=8 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=9 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=10 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=11 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=12 AND year(Date)=2011) 
FROM Rentals 
GROUP BY Book

2 个解决方案

#1

This can be written much simpler by using a CASE expression inside of an aggregate function. This process is called PIVOT:

通过在聚合函数内部使用CASE表达式,可以更简单地编写它。这个过程叫做PIVOT:

select book,
  sum(case when month(Date) = 1 then 1 else 0 end) Jan,
  sum(case when month(Date) = 2 then 1 else 0 end) Feb,
  sum(case when month(Date) = 3 then 1 else 0 end) Mar,
  sum(case when month(Date) = 4 then 1 else 0 end) Apr,
  sum(case when month(Date) = 5 then 1 else 0 end) May,
  sum(case when month(Date) = 6 then 1 else 0 end) Jun,
  sum(case when month(Date) = 7 then 1 else 0 end) Jul,
  sum(case when month(Date) = 8 then 1 else 0 end) Aug,
  sum(case when month(Date) = 9 then 1 else 0 end) Sep,
  sum(case when month(Date) = 10 then 1 else 0 end) Oct,
  sum(case when month(Date) = 11 then 1 else 0 end) Nov,
  sum(case when month(Date) = 12 then 1 else 0 end) Dec
from Rentals
where year(date) = 2011
group by book;

See SQL Fiddle with Demo. Instead of querying the table multiple times for each column, you use conditional aggregation to get the count for each book during the month and year.

请参阅SQL Fiddle with Demo。您可以使用条件聚合来获取每月和每年的每本书的计数,而不是每列多次查询表。

#2

If you use pivot the code is much easier to maintain,

如果使用pivot,代码更容易维护,

SELECT 
    BOOK,
    [1] as Jan ,
    [2] as Feb,
    [3] as Mar,
    [4] as Apr,
    [5] as May,
    [6] as Jun,
    [7] as Jul,
    [8] as Aug,
    [9] as Sep,
    [10] as Oct,
    [11] as Nov,
    [12] as Dec 
FROM
(
    SELECT 
       BOOK , 
       DATEPART(MONTH,[DATE]) AS PER 
    FROM 
        Rentals 
    WHERE 
        DATEPART(YEAR,[DATE]) = 2014
) AS P PIVOT 
    (
    COUNT(PER) FOR PER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS DATA

Simple.

#1