表中有一列order number
A
A
B
B
B
C
C希望再加一列,另一列的数字,在对应相同的order number时,是依次递增的
order number item number
A 1
A 2
B 1
B 2
B 3
C 1
C 1请各位帮忙看,该怎么办
A
A
B
B
B
C
C希望再加一列,另一列的数字,在对应相同的order number时,是依次递增的
order number item number
A 1
A 2
B 1
B 2
B 3
C 1
C 1请各位帮忙看,该怎么办
+
group by order number
drop table tb
Go
Create table tb([order number] nvarchar(10))
Insert tb
select N'A' union all
select N'A' union all
select N'B' union all
select N'B' union all
select N'B' union all
select N'C' union all
select N'C'
Go
Select
[order number]
,row_number()over(partition by [order number] order by (select 1)) as [item number]
from tb
/*
order number item number
------------ --------------------
A 1
A 2
B 1
B 2
B 3
C 1
C 2(7 row(s) affected)
*/
if object_id('tb','u') is not null
drop table tb
go
create table tb
(
col1 varchar(10)
)
insert into tb
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'C' union all
select 'C'
go
select col1,row=row_number() over(partition by col1 order by col1) from tb
/*
col1 row
---------- --------------------
A 1
A 2
B 1
B 2
B 3
C 1
C 2(7 行受影响)
*/
[order number]
,row_number()over(partition by ([order number]) order by getdate()) as [item number]
from tb
'row_number' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.sql_sf的查询语句,执行后怎么出错呢
number AS [item number]
FROM ( SELECT [order number] ,
COUNT(*) rn ,
( SELECT COUNT(*)
FROM dbo.tb AS b
WHERE b.[order number] < a.[order number]
) [item number]
FROM tb a
GROUP BY [order number]
) AS d ,
master.dbo.spt_values
WHERE type = 'P'
AND number >= 1
AND number <= rn
(
col1 varchar(10)
)
insert into tb
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'C' union all
select 'C'
go
select col1,[rows]=ROW_NUMBER()over(partition by col1 order by col1 ) from tb col1 rows
---------- --------------------
A 1
A 2
B 1
B 2
B 3
C 1
C 2(7 行受影响)
drop table ordnum;
create table ordnum(nam char(1),n int)
insert into ordnum(nam) select 'A'
insert into ordnum(nam) select 'A'
insert into ordnum(nam) select 'B'
insert into ordnum(nam) select 'B'
insert into ordnum(nam) select 'B'
insert into ordnum(nam) select 'C'
insert into ordnum(nam) select 'C';SELECT nam,ROW_NUMBER() OVER (partition BY nam order by nam) AS ROWID FROM ordnum
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-25 09:03:31
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([order number] varchar(1))
insert [tb]
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'C' union all
select 'C'
--------------开始查询--------------------------
select ID=identity(int,1,1),* into #tb from tb
select
[order number],
[item number]=(select COUNT(1) from #tb where [order number]=t.[order number] and ID<=t.id)
from
#tb t
drop table #tb
----------------结果----------------------------
/* order number item number
------------ -----------
A 1
A 2
B 1
B 2
B 3
C 1
C 2(7 行受影响)*/