34,590
社区成员
发帖
与我相关
我的任务
分享sql 问题望指教。。
create table tmp (id int,BH int, SL money,xh int)
insert into tmp
select 1,4001,3.22,null
union all
select 2,4001,4.33,null
union all
select 3,4002,5.44,null
union all
select 4, 4002,6.44,null
union all
select 5, 4002,7.44,null
union all
select 6, 4002,8.44,null
union all
select 7, 4002,9.44,null
union all
select 8, 4002,10.44,null
select * from tmp
目前数据：
1 4001 3.22 NULL
2 4001 4.33 NULL
3 4002 5.44 NULL
4 4002 6.44 NULL
5 4002 7.44 NULL
6 4002 8.44 NULL
7 4002 9.44 NULL
8 4002 10.44 NULL
需要转换为：
1 4001 3.22 1
2 4001 4.33 2
3 4002 5.44 1
4 4002 6.44 2
5 4002 7.44 3
6 4002 8.44 4
7 4002 9.44 5
8 4002 10.44 6
主要是序号字段，望各位大侠指教一下，我现在用的是 把不重复的编号 游标逐条更新，根据rownuber,太麻烦。
...全文
请发表友善的回复…
发表回复
liu_r_h 2011-05-05
- 打赏
- 举报
xiexie!!.有用处
haosa 2011-05-05
- 打赏
- 举报
select A.id,A.BH,A.sl,row_number() over (partition by BH order by id) as row from tmp AS A
tangtang89522 2011-05-05
- 打赏
- 举报
谢谢。原来row_number() over (partition by QSLCBH order by CCSZ ) 还可以两个连用呀。。。
haosa 2011-05-05
- 打赏
- 举报
create table tmp (id int,BH int, SL money,xh int)
insert into tmp
select 1,4001,3.22,null
union all
select 2,4001,4.33,null
union all
select 3,4002,5.44,null
union all
select 4, 4002,6.44,null
union all
select 5, 4002,7.44,null
union all
select 6, 4002,8.44,null
union all
select 7, 4002,9.44,null
union all
select 8, 4002,10.44,null
select A.id,A.BH,A.SL,rank() over (partition by BH order by id) as row from tmp AS A
id BH SL row
----------- ----------- --------------------- --------------------
1 4001 3.22 1
2 4001 4.33 2
3 4002 5.44 1
4 4002 6.44 2
5 4002 7.44 3
6 4002 8.44 4
7 4002 9.44 5
8 4002 10.44 6
(8 行受影响)
yubofighting 2011-05-05
- 打赏
- 举报
select id,bh,sl,xh=row_number() over(partition by bh order by id) from tmp
叶子 2011-05-05
- 打赏
- 举报
--如果要更新表中的数据
update tmp
set xh=b.xh from tmp a
left join (select id,xh=row_number() over (partition by bh order by sl )
from tmp) b on a.id=b.id
select * from tmp
guguda2008 2011-05-05
- 打赏
- 举报
IF OBJECT_ID('tmp') IS NOT NULL DROP TABLE tmp
GO
create table tmp (id int,BH int, SL money,xh int)
insert into tmp
select 1,4001,3.22,null
union all
select 2,4001,4.33,null
union all
select 3,4002,5.44,null
union all
select 4, 4002,6.44,null
union all
select 5, 4002,7.44,null
union all
select 6, 4002,8.44,null
union all
select 7, 4002,9.44,null
union all
select 8, 4002,10.44,null
UPDATE T1
SET T1.XH=T2.XH
FROM TMP T1
INNER JOIN (
select ID
,ROW_NUMBER() OVER(PARTITION BY BH ORDER BY SL ASC) AS XH
from tmp T1
) T2 ON T1.ID=T2.ID
SELECT * FROM TMP
/*
1 4001 3.22 1
2 4001 4.33 2
3 4002 5.44 1
4 4002 6.44 2
5 4002 7.44 3
6 4002 8.44 4
7 4002 9.44 5
8 4002 10.44 6
*/
快溜 2011-05-05
- 打赏
- 举报
create table tmp (id int,BH int, SL money,xh int)
insert into tmp
select 1,4001,3.22,null
union all
select 2,4001,4.33,null
union all
select 3,4002,5.44,null
union all
select 4, 4002,6.44,null
union all
select 5, 4002,7.44,null
union all
select 6, 4002,8.44,null
union all
select 7, 4002,9.44,null
union all
select 8, 4002,10.44,null
declare @num int,@num2 int
set @num=0
update tmp set xh=@num,@num=(case when @num2=BH then @num+1 else 1 end),@num2=BH
select * from tmp
/*
id BH SL xh
----------- ----------- --------------------- -----------
1 4001 3.22 1
2 4001 4.33 2
3 4002 5.44 1
4 4002 6.44 2
5 4002 7.44 3
6 4002 8.44 4
7 4002 9.44 5
8 4002 10.44 6叶子 2011-05-05
- 打赏
- 举报
select id,bh,sl,xh=row_number() over (partition by bh order by sl) from tmp
/*
id bh sl xh
----------- ----------- --------------------- --------------------
1 4001 3.22 1
2 4001 4.33 2
3 4002 5.44 1
4 4002 6.44 2
5 4002 7.44 3
6 4002 8.44 4
7 4002 9.44 5
8 4002 10.44 6
*/
