27,580
社区成员
发帖
与我相关
我的任务
分享
create table 表A
(Bill varchar(10), Lot varchar(10))
insert into 表A
select 'A-0001', 'CA01' union all
select 'A-0001', 'CA03' union all
select 'A-0001', 'CB09' union all
select 'A-0002', 'CB02' union all
select 'A-0002', 'CD02' union all
select 'A-0002', 'CQ02' union all
select 'A-0002', 'CQ03' union all
select 'A-0002', 'CW02'
select (select count(1) from 表A b
where b.Bill=a.Bill and b.Lot<=a.Lot) 'ID',
a.Bill,
a.Lot
from 表A a
/*
ID Bill Lot
----------- ---------- ----------
1 A-0001 CA01
2 A-0001 CA03
3 A-0001 CB09
1 A-0002 CB02
2 A-0002 CD02
3 A-0002 CQ02
4 A-0002 CQ03
5 A-0002 CW02
(8 row(s) affected)
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-10 15:32:41
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Bill] varchar(6),[Lot] varchar(4))
insert [A]
select 'A-0001','CA01' union all
select 'A-0001','CA03' union all
select 'A-0001','CB09' union all
select 'A-0002','CB02' union all
select 'A-0002','CD02' union all
select 'A-0002','CQ02' union all
select 'A-0002','CQ03' union all
select 'A-0002','CW02'
--------------开始查询--------------------------
select ROW_NUMBER()OVER(PARTITION BY Bill ORDER BY Lot)id,* from [A]
----------------结果----------------------------
/*
id Bill Lot
-------------------- ------ ----
1 A-0001 CA01
2 A-0001 CA03
3 A-0001 CB09
1 A-0002 CB02
2 A-0002 CD02
3 A-0002 CQ02
4 A-0002 CQ03
5 A-0002 CW02
*/