Question

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

表biao
主键id 上班时间字段 Time 员工号字段usid 数量字段cont
1 2011-9-14 10:51:07 108 60
2 2011-9-14 18:53:34 202 79
3 2011-9-14 18:53:34 108 82

现在要统计本月数量cont值按最多的员工排行
输出本月
第一名:工号108 数量2208
第二名:工号102 数量2007
第三名:工号214 数量1978

查看上月数据

求解

Answer 1

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

追问

数量值怎么输出显示？"&rs("cont")&"这样吗？

追答

是的。输出时只要做简单的字符串连就行了。

追问

Microsoft VBScript 编译器错误 错误 '800a0408'
无效字符
/wap/qi.asp，行 55
declare @currMonth as DateTime

追答

这段不是VBS，而是T-SQL代码，应该将整段代码当成一个SQL语句执行。

Answer 2

select userid, sum(cont) from biao group by usid order by count(cont) where time bewteen 2011-8-31 and 2011-10-1

追问

数量值输出"&rs("cont")&"这样显示出错，请问怎样解决？才发现你这个是统计上班次数，不是以cont值排行的

追答

select userid, sum(cont) as total from biao group by usid order by sum(cont) where time bewteen 2011-8-31 and 2011-10-1
输出用"&rs("total")&"