27,580
社区成员
发帖
与我相关
我的任务
分享
辅助列:
alter table 要查的表 add 辅助列 int identity
go
临时表:
select 辅助列=identity(int),* into #临时表 from 要查的表
具体查询方法上面有了。
create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--2005
select *,rn=row_number()over(partition by ym,name order by OrderNo) from [tb]
--2000
select *,(select COUNT(*) from tb b where a.ym=b.ym and a.name=b.name and a.OrderNo>=b.OrderNo) as rn from [tb] a
order by ym,name,3
YM Name OrderNo rn
----------- ---- ------- -----------
200901 a 014123 1
200901 a 015197 2
200901 a 016200 3
200901 b 014567 1
200901 b 014723 2
200901 c 015145 1
200902 a 015146 1
200902 b 015178 1
200902 c 015100 1
200903 a 014174 1
200903 a 016127 2
(11 行受影响)
---2000
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-06 20:38:41
-- Version:
-- 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]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--------------开始查询--------------------------
select
*,item=(select count(1)+1 from tb where ym=t.ym and name=t.name and OrderNo<t.OrderNo)
from
[tb] t
order by
ym,name
----------------结果----------------------------
/* YM Name OrderNo item
----------- ---- ------- --------------------
200901 a 014123 1
200901 a 015197 2
200901 a 016200 3
200901 b 014567 1
200901 b 014723 2
200901 c 015145 1
200902 a 015146 1
200902 b 015178 1
200902 c 015100 1
200903 a 014174 1
200903 a 016127 2
(11 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-06 20:38:41
-- Version:
-- 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]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--------------开始查询--------------------------
select *,item=row_number()over(partition by ym,name order by OrderNo) from [tb]
----------------结果----------------------------
/* YM Name OrderNo item
----------- ---- ------- --------------------
200901 a 014123 1
200901 a 015197 2
200901 a 016200 3
200901 b 014567 1
200901 b 014723 2
200901 c 015145 1
200902 a 015146 1
200902 b 015178 1
200902 c 015100 1
200903 a 014174 1
200903 a 016127 2
(11 行受影响)
*/