在次求助给每行记录编号的问题

Question

我一个表tb，如下： chexingdaima    xiaoshoudingdan    shuliang    dinggouriqi 
aaa                01                  1        2009-09-01 
aaa                01                  1        2009-09-01 
aaa                01                  1        2009-09-01 
bbb                01                  1        2009-09-01 
bbb                01                  1        2009-09-01 
ccc                01                  1        2009-09-01 
ccc                01                  1        2009-09-01 
aaa                02                  1        2009-09-02 
aaa                02                  1        2009-09-02 
bbb                02                  1        2009-09-02 
bbb                02                  1        2009-09-02 
ccc                02                  1        2009-09-02 
还是这表，但是有重复记录，就是说一个订单有不同的车型，而不行的车型有台数，我这个表是明晰表，把每台车都分成一台一台的。所以有重复记录我想要的结果：chexingdaima    xiaoshoudingdan    shuliang    dinggouriqi      bianhao
aaa                01                  1        2009-09-01        1
aaa                01                  1        2009-09-01        2
aaa                01                  1        2009-09-01        3
bbb                01                  1        2009-09-01        4
bbb                01                  1        2009-09-01        5
ccc                01                  1        2009-09-01        6
ccc                01                  1        2009-09-01        7
aaa                02                  1        2009-09-02        1
aaa                02                  1        2009-09-02        2
bbb                02                  1        2009-09-02        3
bbb                02                  1        2009-09-02        4
ccc                02                  1        2009-09-02        5请各位高手在此帮忙。谢谢。

Answer 1

select 
*,
bianhao=row_number()over(partition by  xiaoshoudingdan order by chexingdaima)
from tb 

Answer 2

不好意思刚才我的小失误
这样写就可以的
--2000
select *,bianhao=(select count(1) from @tb t where t.[xiaoshoudingdan]=a.[xiaoshoudingdan] and t.[chexingdaima]<=a.[chexingdaima] )
from @tb a

Answer 3

select px=identity(int,1,1),* into # from tbselect 
  chexingdaima,xiaoshoudingdan,shuliang,dinggouriqi,
  pianhao=(select count(1)+1 from # where xiaoshoudingdan=t.xiaoshoudingdan and px<t.px)
from
  # tdrop table #     

Answer 4

select *,bianhao = cast(null as int) into #tmp from tb order by xiaoshoudingdan,chexingdaima;declare @xiaoshoudingdan varchar(10);
declare @i int;update #tmp set
    @i = case when xiaoshoudingdan = @xiaoshoudingdan then @i + 1 else 1 end,
    @xiaoshoudingdan = xiaoshoudingdan,
    bianhao = @i;select * from #tmp;

Answer 5

2005直接ROW_NUMBER() OVER (PARTITION BY ORDER BY)
200参考小爱的

Answer 6

--2000
select 
  *,
  bianhao=(select count(1)+1 from tb where xiaoshoudingdan=t.xiaoshoudingdan and chexingdaima<t.chexingdaima)
from
  tb t
--2005
select 
  *,
  bianhao=rank()over(partition by chexingdaima order by dinggouriqi) 
from 
  tb

Answer 7

select 
*,
bianhao=row_number()over(partition by  xiaoshoudingdan order by chexingdaima)
from tb 

Answer 8

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-03 13:46:30
-------------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (chexingdaima VARCHAR(3),xiaoshoudingdan VARCHAR(2),shuliang INT,dinggouriqi DATETIME)
INSERT INTO @tb
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'bbb','01',1,'2009-09-01' UNION ALL
SELECT 'bbb','01',1,'2009-09-01' UNION ALL
SELECT 'ccc','01',1,'2009-09-01' UNION ALL
SELECT 'ccc','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','02',1,'2009-09-02' UNION ALL
SELECT 'aaa','02',1,'2009-09-02' UNION ALL
SELECT 'bbb','02',1,'2009-09-02' UNION ALL
SELECT 'bbb','02',1,'2009-09-02' UNION ALL
SELECT 'ccc','02',1,'2009-09-02'--SQL查询如下:select *,bianhao = cast(null as int) into #tmp from @tb order by xiaoshoudingdan,chexingdaima;declare @xiaoshoudingdan varchar(10);
declare @i int;update #tmp set
    @i = case when xiaoshoudingdan = @xiaoshoudingdan then @i + 1 else 1 end,
    @xiaoshoudingdan = xiaoshoudingdan,
    bianhao = @i;select * from #tmp;drop table #tmp;/*
chexingdaima xiaoshoudingdan shuliang    dinggouriqi             bianhao
------------ --------------- ----------- ----------------------- -----------
aaa          01              1           2009-09-01 00:00:00.000 1
aaa          01              1           2009-09-01 00:00:00.000 2
aaa          01              1           2009-09-01 00:00:00.000 3
bbb          01              1           2009-09-01 00:00:00.000 4
bbb          01              1           2009-09-01 00:00:00.000 5
ccc          01              1           2009-09-01 00:00:00.000 6
ccc          01              1           2009-09-01 00:00:00.000 7
aaa          02              1           2009-09-02 00:00:00.000 1
aaa          02              1           2009-09-02 00:00:00.000 2
bbb          02              1           2009-09-02 00:00:00.000 3
bbb          02              1           2009-09-02 00:00:00.000 4
ccc          02              1           2009-09-02 00:00:00.000 5(12 row(s) affected)*/

Answer 9

2000的写法
select identity(int,1,1) as id ,* into # from tb
select chexingdaima,
xiaoshoudingdan ,
shuliang  ,
dinggouriqi  ,
 bianhao=(select COUNT(*) from # where k.xiaoshoudingdan=xiaoshoudingdan and ID<=k.id) 
 from # k 

Answer 10

create  TABLE tb (chexingdaima VARCHAR(3),xiaoshoudingdan VARCHAR(2),shuliang INT,dinggouriqi DATETIME)
INSERT INTO tb 
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','01',1,'2009-09-01' UNION ALL
SELECT 'bbb','01',1,'2009-09-01' UNION ALL
SELECT 'bbb','01',1,'2009-09-01' UNION ALL
SELECT 'ccc','01',1,'2009-09-01' UNION ALL
SELECT 'ccc','01',1,'2009-09-01' UNION ALL
SELECT 'aaa','02',1,'2009-09-02' UNION ALL
SELECT 'aaa','02',1,'2009-09-02' UNION ALL
SELECT 'bbb','02',1,'2009-09-02' UNION ALL
SELECT 'bbb','02',1,'2009-09-02' UNION ALL
SELECT 'ccc','02',1,'2009-09-02'
select identity(int,1,1) as id ,* into # from tb
select chexingdaima,
xiaoshoudingdan ,
shuliang  ,
dinggouriqi  ,
 bianhao=(select COUNT(*) from # where k.xiaoshoudingdan=xiaoshoudingdan and ID<=k.id) 
 from # k 
 
 chexingdaima xiaoshoudingdan shuliang    dinggouriqi             bianhao
------------ --------------- ----------- ----------------------- -----------
aaa          01              1           2009-09-01 00:00:00.000 1
aaa          01              1           2009-09-01 00:00:00.000 2
aaa          01              1           2009-09-01 00:00:00.000 3
bbb          01              1           2009-09-01 00:00:00.000 4
bbb          01              1           2009-09-01 00:00:00.000 5
ccc          01              1           2009-09-01 00:00:00.000 6
ccc          01              1           2009-09-01 00:00:00.000 7
aaa          02              1           2009-09-02 00:00:00.000 1
aaa          02              1           2009-09-02 00:00:00.000 2
bbb          02              1           2009-09-02 00:00:00.000 3
bbb          02              1           2009-09-02 00:00:00.000 4
ccc          02              1           2009-09-02 00:00:00.000 5

Answer 11

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([chexingdaima] varchar(3),[xiaoshoudingdan] varchar(2),[shuliang] int,[dinggouriqi] datetime)
insert [tb]
select 'aaa','01',1,'2009-09-01' union all
select 'aaa','01',1,'2009-09-01' union all
select 'aaa','01',1,'2009-09-01' union all
select 'bbb','01',1,'2009-09-01' union all
select 'bbb','01',1,'2009-09-01' union all
select 'ccc','01',1,'2009-09-01' union all
select 'ccc','01',1,'2009-09-01' union all
select 'aaa','02',1,'2009-09-02' union all
select 'aaa','02',1,'2009-09-02' union all
select 'bbb','02',1,'2009-09-02' union all
select 'bbb','02',1,'2009-09-02' union all
select 'ccc','02',1,'2009-09-02'
select px=identity(int,1,1),* into # from tbselect 
  chexingdaima,xiaoshoudingdan,shuliang,dinggouriqi,
  pianhao=(select count(1)+1 from # where xiaoshoudingdan=t.xiaoshoudingdan and px<t.px)
from
  # t
/*
chexingdaima xiaoshoudingdan shuliang    dinggouriqi             pianhao
------------ --------------- ----------- ----------------------- -----------
aaa          01              1           2009-09-01 00:00:00.000 1
aaa          01              1           2009-09-01 00:00:00.000 2
aaa          01              1           2009-09-01 00:00:00.000 3
bbb          01              1           2009-09-01 00:00:00.000 4
bbb          01              1           2009-09-01 00:00:00.000 5
ccc          01              1           2009-09-01 00:00:00.000 6
ccc          01              1           2009-09-01 00:00:00.000 7
aaa          02              1           2009-09-02 00:00:00.000 1
aaa          02              1           2009-09-02 00:00:00.000 2
bbb          02              1           2009-09-02 00:00:00.000 3
bbb          02              1           2009-09-02 00:00:00.000 4
ccc          02              1           2009-09-02 00:00:00.000 5*/

Answer 12

select 
*,
bianhao=row_number()over(partition by  xiaoshoudingdan order by chexingdaima)
from tb chexingdaima xiaoshoudingdan shuliang    dinggouriqi             bianhao
------------ --------------- ----------- ----------------------- --------------------
aaa          01              1           2009-09-01 00:00:00.000 1
aaa          01              1           2009-09-01 00:00:00.000 2
aaa          01              1           2009-09-01 00:00:00.000 3
bbb          01              1           2009-09-01 00:00:00.000 4
bbb          01              1           2009-09-01 00:00:00.000 5
ccc          01              1           2009-09-01 00:00:00.000 6
ccc          01              1           2009-09-01 00:00:00.000 7
aaa          02              1           2009-09-02 00:00:00.000 1
aaa          02              1           2009-09-02 00:00:00.000 2
bbb          02              1           2009-09-02 00:00:00.000 3
bbb          02              1           2009-09-02 00:00:00.000 4
ccc          02              1           2009-09-02 00:00:00.000 5(12 行受影响)
这是1楼的2005方法测试

Answer 13

SELECT *,ID=IDENTITY(INT,1,1) INTO #T FROM TB SELECT chexingdaima,xiaoshoudingdan,shuliang, dinggouriqi, 
bianhao=(SELECT COUNT(1) FROM #T WHERE xiaoshoudingdan=T.xiaoshoudingdan AND ID<=T.ID) 
FROM #T TDROP TABLE #T

Answer 14

if object_id('[tb]') is not null drop table [tb]
create table [tb]([chexingdaima] varchar(3),[xiaoshoudingdan] varchar(2),[shuliang] int,[dinggouriqi] datetime)
insert [tb]
select 'aaa','01',1,'2009-09-01' union all
select 'aaa','01',1,'2009-09-01' union all
select 'aaa','01',1,'2009-09-01' union all
select 'bbb','01',1,'2009-09-01' union all
select 'bbb','01',1,'2009-09-01' union all
select 'ccc','01',1,'2009-09-01' union all
select 'ccc','01',1,'2009-09-01' union all
select 'aaa','02',1,'2009-09-02' union all
select 'aaa','02',1,'2009-09-02' union all
select 'bbb','02',1,'2009-09-02' union all
select 'bbb','02',1,'2009-09-02' union all
select 'ccc','02',1,'2009-09-02'SELECT *,ID=IDENTITY(INT,1,1) INTO #T FROM TB SELECT chexingdaima,xiaoshoudingdan,shuliang, dinggouriqi, 
bianhao=(SELECT COUNT(1) FROM #T WHERE xiaoshoudingdan=T.xiaoshoudingdan AND ID<=T.ID) 
FROM #T TDROP TABLE #Tchexingdaima xiaoshoudingdan shuliang    dinggouriqi             bianhao
------------ --------------- ----------- ----------------------- -----------
aaa          01              1           2009-09-01 00:00:00.000 1
aaa          01              1           2009-09-01 00:00:00.000 2
aaa          01              1           2009-09-01 00:00:00.000 3
bbb          01              1           2009-09-01 00:00:00.000 4
bbb          01              1           2009-09-01 00:00:00.000 5
ccc          01              1           2009-09-01 00:00:00.000 6
ccc          01              1           2009-09-01 00:00:00.000 7
aaa          02              1           2009-09-02 00:00:00.000 1
aaa          02              1           2009-09-02 00:00:00.000 2
bbb          02              1           2009-09-02 00:00:00.000 3
bbb          02              1           2009-09-02 00:00:00.000 4
ccc          02              1           2009-09-02 00:00:00.000 5(12 行受影响)

Answer 15

select 
*,
bianhao=row_number()over(partition by  xiaoshoudingdan order by chexingdaima)
from tb 

Answer 16

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-03 14:02:09
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([chexingdaima] nvarchar(3),[xiaoshoudingdan] int,[shuliang] int,[dinggouriqi] nvarchar(10))
Insert [tb]
Select N'aaa',01,1,'2009-09-01' union all
Select N'aaa',01,1,'2009-09-01' union all
Select N'aaa',01,1,'2009-09-01' union all
Select N'bbb',01,1,'2009-09-01' union all
Select N'bbb',01,1,'2009-09-01' union all
Select N'ccc',01,1,'2009-09-01' union all
Select N'ccc',01,1,'2009-09-01' union all
Select N'aaa',02,1,'2009-09-02' union all
Select N'aaa',02,1,'2009-09-02' union all
Select N'bbb',02,1,'2009-09-02' union all
Select N'bbb',02,1,'2009-09-02' union all
Select N'ccc',02,1,'2009-09-02'
Go
--Select * from [tb]-->SQL查询如下:
--2005
select *,
bianhao =row_number()over(partition by [xiaoshoudingdan] order by [chexingdaima],[dinggouriqi])
from tb--2000
alter table tb add id int identity 
go
select [chexingdaima],[xiaoshoudingdan],[shuliang],[dinggouriqi],
bianhao =(select COUNT(1) from tb where [xiaoshoudingdan]=t.[xiaoshoudingdan] and id<=t.id)
from tb t
/*
chexingdaima xiaoshoudingdan shuliang    dinggouriqi bianhao
------------ --------------- ----------- ----------- --------------------
aaa          1               1           2009-09-01  1
aaa          1               1           2009-09-01  2
aaa          1               1           2009-09-01  3
bbb          1               1           2009-09-01  4
bbb          1               1           2009-09-01  5
ccc          1               1           2009-09-01  6
ccc          1               1           2009-09-01  7
aaa          2               1           2009-09-02  1
aaa          2               1           2009-09-02  2
bbb          2               1           2009-09-02  3
bbb          2               1           2009-09-02  4
ccc          2               1           2009-09-02  5(12 行受影响)
*/