需求如下:
表: A
字段: etID 分店编号
TotalNumber 业绩总数
-- 根据业绩总数排序,从大到小
SELECT * FROM A ORDER BY TotalNumber DESC-- 需要得到一个这样的信息
-- 名次 分店编号 业绩总数
-- 1 301 2000
-- 2 203 1000-- 怎样得到?我现在用的解决方案DECLARE @SortID int,
@MarketID int,
@TotalNumber int
SET @SortID = 0
DECLARE CC CURSOR FOR
SELECT MarketID,TotalNumber FROM A ORDER BY TotalNumber DESC
OPEN CC
WHILE(0=0)
BEGIN
FETCH NEXT FROM CC INTO @MarketID,@TotalNumber
IF @@FETCH_STATUS <> 0 BREAK
SET @SortID = @SortID + 1
INSERT INTO returnTable(SortID,MarketID,TotalNumber)
SELECT @SortID,@MarketID,@TotalNumber
END
CLOSE CC
DEALLOCATE CC
return returnTable
表: A
字段: etID 分店编号
TotalNumber 业绩总数
-- 根据业绩总数排序,从大到小
SELECT * FROM A ORDER BY TotalNumber DESC-- 需要得到一个这样的信息
-- 名次 分店编号 业绩总数
-- 1 301 2000
-- 2 203 1000-- 怎样得到?我现在用的解决方案DECLARE @SortID int,
@MarketID int,
@TotalNumber int
SET @SortID = 0
DECLARE CC CURSOR FOR
SELECT MarketID,TotalNumber FROM A ORDER BY TotalNumber DESC
OPEN CC
WHILE(0=0)
BEGIN
FETCH NEXT FROM CC INTO @MarketID,@TotalNumber
IF @@FETCH_STATUS <> 0 BREAK
SET @SortID = @SortID + 1
INSERT INTO returnTable(SortID,MarketID,TotalNumber)
SELECT @SortID,@MarketID,@TotalNumber
END
CLOSE CC
DEALLOCATE CC
return returnTable
1、select id=identity(int,1,1),* into # from A ORDER BY TotalNumber DESC
select * from #
2、select id=(select count(*) from a where 业绩总数>=t.业绩总数) ,* from a t
sqlserver 2005
select row=row_number() over(order by 业绩总数),* from a
select row_number() over (order by业绩总数),* from table
(SELECT COUNT(*) FROM A WHERE TotalNumber >= ta.TotalNumber) AS 名次
FROM A ta
ORDER BY TotalNumber DESC;
Select id,etID,TotalNumber from A order by TotalNumber desc
写的应该没有问题。
etID 分店编号,
TotalNumber 业绩总数,
(SELECT COUNT(*) FROM A WHERE TotalNumber >= ta.TotalNumber) AS 名次
FROM
A ta
ORDER BY
TotalNumber DESC;
select 名次=IDENTITY(INT,1,1),A.etID as 分店编号,A.totalnumber as 业绩总数 INTO TA FROM A
order by totalnumber desc SELECT * FROM TA
-------------结果-------------
/*
名次 分店编号 业绩总数
1 301 2000
2 103 1500
3 203 1000
(三行受影响)
*/
create table A([etID] char(6),[totalnumber] char(6))
insert A
select '301','2000' union all
select '203','1000' union all
select '103','1500'
select 名次=IDENTITY(INT,1,1),A.etID as 分店编号,A.totalnumber as 业绩总数 INTO TA FROM A
order by totalnumber desc SELECT * FROM TA
-------------结果-------------
/*
名次 分店编号 业绩总数
1 301 2000
2 103 1500
3 203 1000
(三行受影响)
*/