1, select distinct identity(int,1,1) as ID,关键列 into temp from tb order by 关键列 2, selct b.id,a.* from tb a,temp b where a.关键列=b.关键列
--示例数据 CREATE TABLE tb(Name varchar(10),Score decimal(10,2)) INSERT tb SELECT 'aa',99 UNION ALL SELECT 'bb',56 UNION ALL SELECT 'cc',56 UNION ALL SELECT 'dd',77 UNION ALL SELECT 'ee',78 UNION ALL SELECT 'ff',76 UNION ALL SELECT 'gg',78 UNION ALL SELECT 'ff',50 GO--1. 名次生成方式1,Score重复时合并名次 SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score) FROM tb a ORDER BY Place /*--结果 Name Score Place ---------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 3 ff 76.00 4 bb 56.00 5 cc 56.00 5 ff 50.00 6 --*/--2. 名次生成方式2,Score重复时保留名次空缺 SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1 FROM tb a ORDER BY Place /*--结果 Name Score Place --------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 4 ff 76.00 5 bb 56.00 6 cc 56.00 6 ff 50.00 8 --*/
是这个意思 吧---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-27 10:41:24 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([aa] varchar(1)) insert [tb] select 'a' union all select 'a' union all select 'b' union all select 'b' union all select 'c' union all select 'c' union all select 'd' union all select 'd' --------------开始查询-------------------------- alter table tb add id int identity go select aa,id=(select count(distinct aa)+1 from tb where aa=t.aa and id<t.id)from tb t ----------------结果---------------------------- /* aa id ---- ----------- a 1 a 2 b 1 b 2 c 1 c 2 d 1 d 2(所影响的行数为 8 行) */
是这样的: 原始表是:select SelfeAd,Ad_id from PerformanceCategories 但是我要进行筛选之后再排名 select SelfeAd,Ad_id from PerformanceCategories where SeasonCode='API' order by SelfeAd
可以参考一下下面的 SELECT A.id,Count(A.ID) as iCount,IDENTITY(int,1,1) as iSeq INTO #TestTmp FROM Test A LEFT JOIN Test B ON A.Qty>=B.Qty GROUP BY A.id ORDER BY iCountSELECT * FROM #TestTmp ORDER BY iSeq
1,
select distinct identity(int,1,1) as ID,关键列 into temp from tb
order by 关键列
2,
selct b.id,a.* from tb a,temp b
where a.关键列=b.关键列
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
--*/--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
--*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 10:41:24
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aa] varchar(1))
insert [tb]
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'd' union all
select 'd'
--------------开始查询--------------------------
alter table tb
add id int identity
go
select aa,id=(select count(distinct aa)+1 from tb where aa=t.aa and id<t.id)from tb t
----------------结果----------------------------
/*
aa id
---- -----------
a 1
a 2
b 1
b 2
c 1
c 2
d 1
d 2(所影响的行数为 8 行)
*/
是这样的:
原始表是:select SelfeAd,Ad_id from PerformanceCategories
但是我要进行筛选之后再排名
select SelfeAd,Ad_id from PerformanceCategories where SeasonCode='API' order by SelfeAd
SELECT A.id,Count(A.ID) as iCount,IDENTITY(int,1,1) as iSeq
INTO #TestTmp
FROM Test A
LEFT JOIN Test B ON A.Qty>=B.Qty
GROUP BY A.id
ORDER BY iCountSELECT * FROM #TestTmp ORDER BY iSeq