sql-server - 每月的SQL查询

Question

我正在尝试按月搜索的 sql 查询

比如我的 table 是这样的

Username   visited  Visisted_Dated

   A          1       01/11/2010
   B          1       10/11/2010
   A          1       03/12/2010
   B          1       06/12/2010
   B          1       06/12/2010
   A          1       03/02/2011 
   B          1       05/02/2011
   A          1       11/03/2011
   A          1       20/03/2011
   B          1       01/03/2011

现在，如果我想在 2 月到 4 月之间搜索用户的访问次数，我需要将输出作为

 Users Nov_2010  Dec_2010   Jan_2011   Feb_2011  March_2011 
   A       1         1        0           1           2               
   B       1         2        0           1           1         

请告诉我如何进行。

谢谢

Answer 1

恐怕你必须使用动态 SQL:

DECLARE @Sql VARCHAR(8000)
DECLARE @ColumnNames VARCHAR(1000)
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SET @BeginDate = '2010-11-1'
SET @EndDate = '2011-4-1'
SET @ColumnNames = ''

WHILE @BeginDate <= @EndDate
BEGIN
    SET @ColumnNames = @ColumnNames + ',[' + DateName(month,@BeginDate) + '_' + Cast(Year(@BeginDate) AS VARCHAR(4)) + ']'
    SET @BeginDate = DateAdd(Month, 1, @BeginDate)
END
IF Len(@ColumnNames) > 0
    SET @ColumnNames = Right(@ColumnNames, Len(@ColumnNames) - 1)

PRINT @ColumnNames

SET @Sql = '
WITH U AS
(
    SELECT UserName, DateName(month,Visited_Dated) + ''_'' + Cast(Year(Visited_Dated) AS VARCHAR(4)) AS VisitedMonth, Visited
    FROM Users
)
SELECT *
FROM U
PIVOT (
    SUM(Visited) FOR VisitedMonth IN (' + @ColumnNames + ')
) AS P'

EXEC (@Sql)