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
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
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
AS
SET NOCOUNT ON
select 歌曲排名=identity(int,1,1),a.* into #tt from( SELECT TOP 100 DownName AS 歌曲名称,DownCount AS 下载次数
FROM MM_Song_Down )a
ORDER BY DownCount DESC
select * from #tt
GO
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
/*
歌曲排名 歌曲名称 下载次数
--------- --------- --------
1 AAA 277
2 BBB 256
3 CCC 256
4 DDD 143
*/
insert MM_Song_Down select 'AAA', 277
union all select 'BBB', 256
union all select 'CCC', 143 CREATE PROC [mm_Down_Top]
AS
SET NOCOUNT ON
SELECT TOP 100 歌曲排名=identity(int, 1, 1), DownName AS 歌曲名称,DownCount AS 下载次数 into #T
FROM MM_Song_Down
ORDER BY DownCount DESC select * from #T
GOexec mm_Down_Top--result
歌曲排名 歌曲名称 下载次数
----------- ---------- -----------
1 AAA 277
2 BBB 256
3 CCC 143