34,575
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-08-30 20:18:42
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,1,12 union all
select 1,1,13 union all
select 1,1,14 union all
select 1,2,15 union all
select 1,2,15 union all
select 1,2,17 union all
select 1,2,18
--------------开始查询--------------------------
select *,
seq=(SELECT COUNT(c) FROM tb WHERE c<a.c and b=a.b)+1 from tb a order by c
----------------小F的结果----------------------------
/*
1 1 12 1
1 1 13 2
1 1 14 3
1 2 15 1
1 2 15 1 ****这里的数字有问题****
1 2 17 3
1 2 18 4
*/
select iDentity(int,1,1) 'ID',*
INTO #TEMP from tb a order by c
SELECT *,
seq=(SELECT COUNT(c) FROM #TEMP WHERE ID<a.ID and a=a.a and b=a.b)+1
FROM #TEMP A order by c
DROP TABLE #TEMP
----------------我的结果----------------------------
/* A B C seq
1 1 1 12 1
2 1 1 13 2
3 1 1 14 3
4 1 2 15 1
5 1 2 15 2
6 1 2 17 3
7 1 2 18 4
*/
select *,
seq=(SELECT COUNT(c) FROM t WHERE c<a.c and a=a.a and b=a.b)+1 from t a
order by c
select *,row_number() over (partition by b order by a) as seq
from t
select *,
seq=(SELECT COUNT(c) FROM tb WHERE c<a.c and a=a.a and b=a.b)+1 from tb a order by c
--tony
--> 生成测试数据表
If not object_id('[t]') is null
Drop table [t]
Go
Create table [t]([A] int,[B] int,[C] int)
Insert t
Select 1,1,12 union all
Select 1,1,13 union all
Select 1,1,14 union all
Select 1,2,15 union all
Select 1,2,16 union all
Select 1,2,17 union all
Select 1,2,18
Go
--select * from t
select *,row_number() over (partition by b order by a)
from t
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-08-30 20:18:42
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,1,12 union all
select 1,1,13 union all
select 1,1,14 union all
select 1,2,15 union all
select 1,2,16 union all
select 1,2,17 union all
select 1,2,18
--------------开始查询--------------------------
select *,
seq=(SELECT COUNT(c) FROM tb WHERE c<a.c and b=a.b)+1 from tb a order by c
----------------结果----------------------------
/* A B C seq
----------- ----------- ----------- -----------
1 1 12 1
1 1 13 2
1 1 14 3
1 2 15 1
1 2 16 2
1 2 17 3
1 2 18 4
(7 行受影响)
*/
select *,row_number over(order by a,b) as seq
from tb
order by a,b