学步园

/*create:zhouhai*/
/*2008-10-28)*/
/*查询当月每天注册的简历数*/
WITH persons_temp AS
(
    SELECT personID,[name],createDate,DAY(createDate) [day] FROM b_persons
    WHERE DATEDIFF(m,GETDATE(),createDate)=0
)
,persons_temp_rowCount AS
(
    SELECT personID,[name],createDate,[day],
    RANK=ROW_NUMBER() OVER (PARTITION BY [day] ORDER BY createDate DESC)
    FROM persons_temp
)
SELECT [day],MAX(RANK) rowCountPerDay FROM persons_temp_rowCount GROUP BY [day] ORDER BY [day] ASC