[求教]SQL语句一难题

雷肿么了 2009-12-16 01:34:48

在SQL2005中,
根据多表连接查询出来的结果如下:
colomn1 colomn2
111111 'aaa'
111111 'bbb'
222222 'ddd'
222222 'eee'
222222 'fff'
333333 'ccc'

我想实现查询出来的时候可以根据colomn1来加一标识列colomn3 如下:
colomn1 colomn2 colomn3
111111 'aaa' 1
111111 'bbb' 2
222222 'ddd' 1
222222 'eee' 2
222222 'fff' 3
333333 'ccc' 1

请问sql2005上如何实现?

...全文

85 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

雷肿么了 2009-12-16

打赏
举报

3Q楼上各位,,结贴..

bancxc 2009-12-16

打赏
举报

select colomn1 ,colomn2 ,

colomn3=row_number() over(partition by colomn1 order by getdate())

from (

select colomn1='111111',colomn2='aaa' union all

select '111111',  'bbb' union all

select '222222',  'ddd' union all

select '222222',  'eee' union all

select '222222',  'fff' union all

select '333333',  'ccc' 

) t

colomn1 colomn2 colomn3

------- ------- --------------------

111111  bbb     1

111111  aaa     2

222222  eee     1

222222  fff     2

222222  ddd     3

333333  ccc     1



(6 行受影响)

--小F-- 2009-12-16

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-12-16 13:40:42

-- 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.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([colomn1] int,[colomn2] varchar(3))

insert [tb]

select 111111,'aaa' union all

select 111111,'bbb' union all

select 222222,'ddd' union all

select 222222,'eee' union all

select 222222,'fff' union all

select 333333,'ccc'

--------------开始查询--------------------------

select

 colomn1 ,colomn2 ,

 colomn3=row_number() over(partition by colomn1 order by getdate())

from 

 tb

----------------结果----------------------------

/* colomn1     colomn2 colomn3

----------- ------- --------------------

111111      aaa     1

111111      bbb     2

222222      ddd     1

222222      eee     2

222222      fff     3

333333      ccc     1



(6 行受影响)

*/

SQL77 2009-12-16

打赏
举报

SELECT *,

(SELECT COUNT(*) FROM TB WHERE colomn1 =T.colomn1 AND colomn2<=T.colomn2)AS colomn3

 FROM TB T

長胸為富 2009-12-16

打赏
举报

Create table #tb(colomn1 int,colomn2 varchar(5))

insert into #tb

select 111111,'aaa' 

union all select 111111,  'bbb' 

union all select   222222,  'ddd' 

union all select   222222,  'eee' 

union all select   222222,  'fff' 

union all select   333333 , 'ccc' 



select colomn1,colomn2,colomn3 = row_number() over(partition by colomn1 order by colomn2 ) from #tb



/*

colomn1	colomn2	colomn3

111111	aaa	1

111111	bbb	2

222222	ddd	1

222222	eee	2

222222	fff	3

333333	ccc	1

*/

bancxc 2009-12-16

打赏
举报

select colomn1 ,colomn2 ,

colomn3=row_number() over(partition by column1 order by getdate())

from 你的结果集

sgtzzc 2009-12-16

打赏
举报

sql2005 用cte

华夏小卒 2009-12-16

打赏
举报

---------------------------------------------

--> Author : js_szy							

--> Target : ★★★						

--> Date   : 2009-12-16 13:37:28

--> Version: SQL Server 2005

---------------------------------------------

											    

--> 测试数据: @tb

declare @tb table (colomn1 int,colomn2 varchar(3))

insert into @tb

select 111111,'aaa' union all

select 111111,'bbb' union all

select 222222,'ddd' union all

select 222222,'eee' union all

select 222222,'fff' union all

select 333333,'ccc'



select *,column3=row_number()over(partition by colomn1 order by getdate()) from @tb





colomn1     colomn2 column3

----------- ------- --------------------

111111      aaa     1

111111      bbb     2

222222      ddd     1

222222      eee     2

222222      fff     3

333333      ccc     1



(6 行受影响)

nianran520 2009-12-16

打赏
举报

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb]([colomn1] int,[colomn2] varchar(3))

insert [tb]

select 111111,'aaa' union all

select 111111,'bbb' union all

select 222222,'ddd' union all

select 222222,'eee' union all

select 222222,'fff' union all

select 333333,'ccc'



select *,

(select count(1) from tb where [colomn1]=t.[colomn1] and [colomn2]<=t.[colomn2]) as colomn3

from [tb] t



------------------------

111111	aaa	1

111111	bbb	2

222222	ddd	1

222222	eee	2

222222	fff	3

333333	ccc	1