34,597
社区成员
发帖
与我相关
我的任务
分享SQL难题请大家进来帮忙谢谢！
请教怎么用游标或其他处理办法达到以下效果（a字段相同时累加赋值），谢谢！
原表
a b 都为int
----------------------------
202 0
202 0
202 0
209 0
207 0
207 0
100 0
100 0
100 0
100 0
效果表以下
a b 都为int
----------------------------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
原表
a b 都为int
----------------------------
202 0
202 0
202 0
209 0
207 0
207 0
100 0
100 0
100 0
100 0
效果表以下
a b 都为int
----------------------------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
...全文
请发表友善的回复…
发表回复
SQLCenter 2010-09-09
- 打赏
- 举报
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(a int, b int)
insert into #
select 202, 0 union all
select 202, 0 union all
select 202, 0 union all
select 209, 0 union all
select 207, 0 union all
select 207, 0 union all
select 100, 0 union all
select 100, 0 union all
select 100, 0 union all
select 100, 0
declare @a int, @b int
update # set @b = case when @a=a then @b+1 else 1 end, b = @b, @a = a
select * from #
/*
a b
----------- -----------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
*/
ganchunsaixx 2010-09-09
- 打赏
- 举报
学习了
「已注销」 2010-09-09
- 打赏
- 举报
DROP TABLE TAB
CREATE TABLE TAB
(a INT,b INT)
INSERT INTO tab
SELECT 202 ,0
UNION ALL
SELECT
202, 0
UNION ALL
SELECT 202, 0
UNION ALL
SELECT 209, 0
UNION ALL
SELECT 207 ,0
UNION ALL
SELECT 207 ,0
UNION ALL
SELECT 100 ,0
UNION ALL
SELECT 100, 0
UNION ALL
SELECT 100, 0
UNION ALL
SELECT 100, 0
SELECT t.a,ROW_NUMBER()OVER(partition by a ORDER BY GETDATE()) b
FROM TAB t
a b
----------- --------------------
100 1
100 2
100 3
100 4
202 1
202 2
202 3
207 1
207 2
209 1
(10 row(s) affected)
百年树人 2010-09-09
- 打赏
- 举报
[Quote=引用 5 楼 icuso 的回复:]
我用的是MSSQL2000
[/Quote]
我用的是MSSQL2000
[/Quote]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 202,0 union all
select 202,0 union all
select 202,0 union all
select 209,0 union all
select 207,0 union all
select 207,0 union all
select 100,0 union all
select 100,0 union all
select 100,0 union all
select 100,0
go
alter table tb add tid int identity(1,1);
go
update t
set b=(select count(1)+1 from tb where a=t.a and tid<t.tid)
from tb t
go
alter table tb drop column tid;
go
select * from tb
/**
a b
----------- -----------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
(10 行受影响)
**/
icuso 2010-09-09
- 打赏
- 举报
我用的是MSSQL2000
icuso 2010-09-09
- 打赏
- 举报
把B列UPDATE成
效果表以下
a b 都为int
----------------------------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
效果表以下
a b 都为int
----------------------------
202 1
202 2
202 3
209 1
207 1
207 2
100 1
100 2
100 3
100 4
obuntu 2010-09-09
- 打赏
- 举报
select
a,
b=row_number() over(partition by a order by (select 0))
from
tb
百年树人 2010-09-09
- 打赏
- 举报
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 202,0 union all
select 202,0 union all
select 202,0 union all
select 209,0 union all
select 207,0 union all
select 207,0 union all
select 100,0 union all
select 100,0 union all
select 100,0 union all
select 100,0
---查询---
select
a,
b=row_number() over(partition by a order by getdate())
from
tb
---结果---
a b
----------- --------------------
100 1
100 2
100 3
100 4
202 1
202 2
202 3
207 1
207 2
209 1
(10 行受影响)百年树人 2010-09-09
- 打赏
- 举报
select
a,
b=row_number() over(partition by a order by getdate())
from
tb
