新手。sql排行问题求帮助
本帖最后由 charrys 于 2012-12-08 19:44:06 编辑
表名为orders
times为添加记录的日期
artid为主要排行ID，每天artid只添加了一个字段，
不断递加count，第二天相同的artid又添加了一个记录，依此类推，我现在要算出这个表的排行，count总和为排行标准。
按最近的3天内排行
求SQL！
[最优解释]

select row_number() over(order by sum([count]) desc) as 排名,artid,sum([count]) as 总和
from tb
where datediff(dd,times,getdate()) between 0 and 2
group by artid
order by sum([count]) desc

[其他解释]

引用:

我的是access2003

Access 2003数据库不支持row_number()这种写法
[其他解释]

引用:

引用:
引用:我的是access2003Access 2003数据库不支持row_number()这种写法
那该如何能得到想要的结果呢

在MSSQL下用1楼是可以的，Access 2003就不太清楚了
[其他解释]
我用的 SQL 不好意思 我做了 你用不了
[其他解释]

select artid,count_sum,
(
select SUM(1)
from
(
select artid,SUM(count)as count_sum
from orders
where datediff(dd,times,getdate()) between 0 and 2
group by artid
) as b
where a.count_sum>=b.count_sum 
) as ranks
from 
(
select artid,SUM(count)as count_sum
from orders
where datediff(dd,times,getdate()) between 0 and 2
group by artid
) as a

[其他解释]
select row_number() over(order by sum([count]) desc) as 排名,artid,sum([count]) as 总和from tbwhere datediff(dd,times,getdate()) between 0 and 2group by artidorder by sum([count]) desc
[其他解释]

引用:

SQL code?12345select row_number() over(order by sum([count]) desc) as 排名,artid,sum([count]) as 总和from tbwhere datediff(dd,times,getdate()) between 0 and 2group by artidorder by sum([count……

版主，你是用什么数据库的。
[其他解释]

引用:

SQL code?12345select row_number() over(order by sum([count]) desc) as 排名,artid,sum([count]) as 总和from tbwhere datediff(dd,times,getdate()) between 0 and 2group by artidorder by sum([count……

[其他解释]
我的是access2003
[其他解释]

引用:

引用:我的是access2003Access 2003数据库不支持row_number()这种写法

那该如何能得到想要的结果呢
[其他解释]
如何选出artid不重复呢？
[其他解释]

Select Distinct a.artid,sum(a.counts) as 总数,b.title as titles From orders a inner join art b on a.artid=b.id where a.times>=Now()-7 and typ='a' group by a.artid,b.title

搞定。