能否在查询结果中虚拟一个自增量的排序字段
CREATE PROC [mm_Down_Top]
AS
SET NOCOUNT ON
SELECT TOP 100 DownName AS 歌曲名称,DownCount AS 下载次数
FROM MM_Song_Down
ORDER BY DownCount DESC
GO
得到结果如下:
歌曲名称 下载次数
--------- --------
AAA 277
BBB 256
CCC 143
能不能在查询结果中虚拟一个自增量的排序字段,得到如下结果呢
歌曲排名 歌曲名称 下载次数
--------- --------- --------
1 AAA 277
2 BBB 256
3 CCC 143
[解决办法]
CREATE PROC [mm_Down_Top]
AS
SET NOCOUNT ON
select 歌曲排名=identity(int,1,1),a.* from( SELECT TOP 100 DownName AS 歌曲名称,DownCount AS 下载次数
FROM MM_Song_Down )a
ORDER BY DownCount DESC
GO
[解决办法]
--try
CREATE PROC [mm_Down_Top]
AS
SET NOCOUNT ON
SELECT TOP 100 ID=identity(int, 1, 1), DownName AS 歌曲名称,DownCount AS 下载次数 into #T
FROM MM_Song_Down
ORDER BY DownCount DESC
select * from #T
GO
[解决办法]
--假设下载次数有重复，会按歌曲名称排名
select *,
isnull((select count(*) from MM_Song_Down
where (DownCount> t.DownCount or (DownCount=t.DownCount and DownName <t.DownName))),0)+1 as orderid
from MM_Song_Down t
order by orderid
