mssql数据库asp排行统计数据（按月份查询排行）

declare @currMonth as DateTime

declare @prevMonth as DateTime

set @currMonth = cast(cast(year(getdate()) as varchar(4)) + right('0'+cast(month(getdate()) as varchar (2)),2) + '01' as DateTime);

set @prevMonth = DATEADD(month,-1,@currMonth)

--本月

select usid, SUM(cont) as total_cont from biao where [Time] >= @currMonth and [Time] < DATEADD(month,1,@currMonth)

group by usid

order by total_cont desc

--上月

select usid, SUM(cont) as total_cont from biao where [Time] >= @prevMonth and [Time] < @currMonth

group by usid

order by total_cont desc

如果希望用一列专门显示名次，可以使用mssql 2005提供的rank()函数。例如：本月可以这么写

select rank() over(order by sum(cont) desc) as rnk,usid, SUM(cont) as total_cont from biao where [Time] >= @currMonth and [Time] < DATEADD(month,1,@currMonth)

group by usid

order by total_cont desc

SELECT count( ) , left( creattime, 7 ) AS left7time

FROM register

WHERE `creattime` LIKE '2012%'

GROUP BY lefttime

select from saleorder where to_char(下单日期，'yyyy') = -- 年份查询

select from saleorder where to_char(下单日期，'yyyy') = and to_char(下单日期,'MM') = --某年中的那一月份查询

select from saleorder where to_char(下单日期，'yyyy') = and to_char(下单日期,'Q') = --某年中的那一季度查询

麻烦在于假设一个月一条也没有的情况，最好是先构造月，由此避免这种现象

select convert(varchar(6),日期,112),count(col) from table group by convert(varchar(6),日期,112)

或者构造月实现(以2013年为例)

select amon,count(bcol) from

(

SELECT '201301' AS mon

UNION ALL

SELECT '201302' AS mon

UNION ALL

SELECT '201303' AS mon

UNION ALL

SELECT '201304' AS mon

UNION ALL

SELECT '201305' AS mon

UNION ALL

SELECT '201306' AS mon

UNION ALL

SELECT '201307' AS mon

UNION ALL

SELECT '201308' AS mon

UNION ALL

SELECT '201309' AS mon) a left join table b on amon=convert(varchar(6),日期,112)

group by amon

以上就是关于mssql数据库asp排行统计数据（按月份查询排行）全部的内容，包括:mssql数据库asp排行统计数据（按月份查询排行）、mysql 按月份分类统计 查询语句、根据年份，月份，季度怎么查询gridview中的数据，数据库字段中只有一个时间字段；等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！