求SQL语句

骑牛上铂金 2010-05-13 03:05:06

表：
sid sMemo
0001 1
0001 1
0001 1
0002 1
0002 1
0002 1
0002 1
0002 1
0002 2
0002 2
0002 2
0002 2
0002 2
0002 3
0003 1
0003 1
0004 1
0004 1
0004 1
0004 1
0004 1
0004 2

请问一下如何自动更新sMemo的值，规则是sid列有1~5行相同值,sMemo值为1，sId如有6~10行相同值，sMemo前五行值为1后5行为2，依此类推，每五行相同值，sMemo值加1

...全文

96 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

Cathy123lee 2010-05-14

打赏
举报

我是来学习的，嘿嘿..

骑牛上铂金 2010-05-14

打赏
举报

with 和row_number都必须在2005下才能用，在sql2000下怎么写呢?

骑牛上铂金 2010-05-13

打赏
举报

mssql2000 下可以执行吗？

永生天地 2010-05-13

打赏
举报

学习蹭分外加不知道都在做什么。。。

黄_瓜 2010-05-13

打赏
举报

应该是这样的，不知道你是不是05的

--> 测试数据：[tb]

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

go

create table [tb]([sid] varchar(4),[sMemo] int)

insert [tb]

select '0001',1 union all

select '0001',1 union all

select '0001',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',3 union all

select '0003',1 union all

select '0003',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',2



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

--2005

;with t as(

select *,row=(row_number() over (partition by [sid]order by sid)+4)/5 from [tb]

)

update t set sMemo=row

select * from tb

/*

sid  sMemo       row

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

0001 1           1

0001 1           1

0001 1           1

0002 1           1

0002 1           1

0002 1           1

0002 1           1

0002 1           1

0002 2           2

0002 2           2

0002 2           2

0002 2           2

0002 2           2

0002 3           3

0003 1           1

0003 1           1

0004 1           1

0004 1           1

0004 1           1

0004 1           1

0004 1           1

0004 2           2



(22 行受影响)









*/

sql_sf 2010-05-13

打赏
举报

我是来膜拜的

东那个升 2010-05-13

打赏
举报



declare @t table(sid char(4),sMemo int)

insert @t select '0001',1

insert @t select '0001',1

insert @t select '0001',1

insert @t select '0002',1

insert @t select '0002',1

insert @t select '0002',1

insert @t select '0002',1

insert @t select '0002',1

insert @t select '0002',2

insert @t select '0002',2

insert @t select '0002',2

insert @t select '0002',2

insert @t select '0002',2

insert @t select '0002',3

insert @t select '0003',1

insert @t select '0003',1

insert @t select '0004',1

insert @t select '0004',1

insert @t select '0004',1

insert @t select '0004',1

insert @t select '0004',1

insert @t select '0004',2



;with cte as(select *,(row_number() over(partition by sid order by getdate())+4)/5 as rn  from @t)



update cte set sMemo=rn

黄_瓜 2010-05-13

打赏
举报

是这样吗？

--> 测试数据：[tb]

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

go

create table [tb]([sid] varchar(4),[sMemo] int)

insert [tb]

select '0001',1 union all

select '0001',1 union all

select '0001',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',1 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',2 union all

select '0002',3 union all

select '0003',1 union all

select '0003',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',1 union all

select '0004',2



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

--2005

;with t as(

select *,row=(row_number() over (order by sid)-1)/5 from [tb]

)

update t set sMemo=row+1

select * from tb

/*

sid  sMemo

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

0001 1

0001 1

0001 1

0002 1

0002 1

0002 2

0002 2

0002 2

0002 2

0002 2

0002 3

0002 3

0002 3

0002 3

0003 3

0003 4

0004 4

0004 4

0004 4

0004 4

0004 5

0004 5



(22 行受影响)





*/

htl258_Tony 2010-05-13

打赏
举报

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb] ([sid] [nvarchar](10),[sMemo] [int])

INSERT INTO [tb]

SELECT '0001',null UNION ALL

SELECT '0001',null UNION ALL

SELECT '0001',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0002',null UNION ALL

SELECT '0003',null UNION ALL

SELECT '0003',null UNION ALL

SELECT '0004',null UNION ALL

SELECT '0004',null UNION ALL

SELECT '0004',null UNION ALL

SELECT '0004',null UNION ALL

SELECT '0004',null UNION ALL

SELECT '0004',null



--SELECT * FROM [tb]



-->SQL查询如下:

;with t as

(

	select rn=row_number()over(partition by sid order by sid),*

	from tb

)

update t set sMemo=(rn+4)/5



select * from tb

/*

sid        sMemo

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

0001       1

0001       1

0001       1

0002       1

0002       1

0002       1

0002       1

0002       1

0002       2

0002       2

0002       2

0002       2

0002       2

0002       3

0003       1

0003       1

0004       1

0004       1

0004       1

0004       1

0004       1

0004       2



(22 行受影响)

*/

htl258_Tony 2010-05-13

打赏
举报

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

-- Author : htl258(Tony)

-- Date   : 2010-05-13 15:33:11

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog   : http://blog.csdn.net/htl258

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



--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb] ([sid] [nvarchar](10),[sMemo] [int])

INSERT INTO [tb]

SELECT '0001','1' UNION ALL

SELECT '0001','1' UNION ALL

SELECT '0001','1' UNION ALL

SELECT '0002','1' UNION ALL

SELECT '0002','1' UNION ALL

SELECT '0002','1' UNION ALL

SELECT '0002','1' UNION ALL

SELECT '0002','1' UNION ALL

SELECT '0002','2' UNION ALL

SELECT '0002','2' UNION ALL

SELECT '0002','2' UNION ALL

SELECT '0002','2' UNION ALL

SELECT '0002','2' UNION ALL

SELECT '0002','3' UNION ALL

SELECT '0003','1' UNION ALL

SELECT '0003','1' UNION ALL

SELECT '0004','1' UNION ALL

SELECT '0004','1' UNION ALL

SELECT '0004','1' UNION ALL

SELECT '0004','1' UNION ALL

SELECT '0004','1' UNION ALL

SELECT '0004','2'



--SELECT * FROM [tb]



-->SQL查询如下:

;with t as

(

	select rn=row_number()over(partition by sid order by sid),*

	from tb

)

update t set sMemo=(rn+4)/5



select * from tb

/*

sid        sMemo

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

0001       1

0001       1

0001       1

0002       1

0002       1

0002       1

0002       1

0002       1

0002       2

0002       2

0002       2

0002       2

0002       2

0002       3

0003       1

0003       1

0004       1

0004       1

0004       1

0004       1

0004       1

0004       2



(22 行受影响)

*/