求一句sql（update... top 1...group by...order by ）

ohmydog1 2010-04-01 04:45:44

要更新指定条件的数据行，要求只更新group by.... order by....后的各group第一条数据

id-----uid----objid----dowhat---score-------createtime
1-------1------1--------评论------0-------2010-11-11 11:11:11
2-------1------1--------评论------0-------2010-01-11 11:11:11
3-------1------2--------评论------0-------2010-02-11 11:11:11
4-------2------1--------评论------0-------2010-05-11 11:11:11
5-------2------2--------收藏------0-------2010-03-11 11:11:11
6-------2------2--------评论------0-------2010-04-11 11:11:11
7-------2------1--------评论------0-------2010-12-11 11:11:11
8-------1------2--------评论------0-------2010-06-11 11:11:11
9-------2------2--------评论------0-------2010-07-11 11:11:11

要求：
1：更新同一uid 对同一objid 进行的第一次评论的score=2

...全文

1592 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

ohmydog1 2010-04-01

打赏
举报

谢谢！！！
5# 6# 都能用
6# order by 一点小问题。应该是createtime吧

--小F-- 2010-04-01

打赏
举报

--sorry 分组弄错

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

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

-- Date    :2010-04-01 16:57:21

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 

--	May 26 2009 14:24:20 

--	Copyright (c) 1988-2005 Microsoft Corporation

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

--

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

--> 测试数据：[tb]

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

go 

create table [tb]([id] int,[uid] int,[objid] int,[dowhat] varchar(4),[score] int,[createtime] datetime)

insert [tb]

select 1,1,1,'评论',0,'2010-11-11 11:11:11' union all

select 2,1,1,'评论',0,'2010-01-11 11:11:11' union all

select 3,1,2,'评论',0,'2010-02-11 11:11:11' union all

select 4,2,1,'评论',0,'2010-05-11 11:11:11' union all

select 5,2,2,'收藏',0,'2010-03-11 11:11:11' union all

select 6,2,2,'评论',0,'2010-04-11 11:11:11' union all

select 7,2,1,'评论',0,'2010-12-11 11:11:11' union all

select 8,1,2,'评论',0,'2010-06-11 11:11:11' union all

select 9,2,2,'评论',0,'2010-07-11 11:11:11'

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

update 

  b

set

  score=2

from

  tb a,

  (select id0=row_number()over(partition by uid,objid order by getdate()),* from tb where dowhat='评论')b

where

  a.uid=b.uid

and

  b.id0=1



select * from tb

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

/* id          uid         objid       dowhat score       createtime

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

1           1           1           评论     2           2010-11-11 11:11:11.000

2           1           1           评论     0           2010-01-11 11:11:11.000

3           1           2           评论     2           2010-02-11 11:11:11.000

4           2           1           评论     0           2010-05-11 11:11:11.000

5           2           2           收藏     0           2010-03-11 11:11:11.000

6           2           2           评论     2           2010-04-11 11:11:11.000

7           2           1           评论     2           2010-12-11 11:11:11.000

8           1           2           评论     0           2010-06-11 11:11:11.000

9           2           2           评论     0           2010-07-11 11:11:11.000



(9 行受影响)

*/

dawugui 2010-04-01

打赏
举报

update tb

set score = 2

from tb t

where dowhat = '评论' and createtime = (select min(createtime) from tb where uid = t.uid and objid = t.objid and dowhat = '评论')

--小F-- 2010-04-01

打赏
举报

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

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

-- Date    :2010-04-01 16:57:21

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 

--	May 26 2009 14:24:20 

--	Copyright (c) 1988-2005 Microsoft Corporation

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

--

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

--> 测试数据：[tb]

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

go 

create table [tb]([id] int,[uid] int,[objid] int,[dowhat] varchar(4),[score] int,[createtime] datetime)

insert [tb]

select 1,1,1,'评论',0,'2010-11-11 11:11:11' union all

select 2,1,1,'评论',0,'2010-01-11 11:11:11' union all

select 3,1,2,'评论',0,'2010-02-11 11:11:11' union all

select 4,2,1,'评论',0,'2010-05-11 11:11:11' union all

select 5,2,2,'收藏',0,'2010-03-11 11:11:11' union all

select 6,2,2,'评论',0,'2010-04-11 11:11:11' union all

select 7,2,1,'评论',0,'2010-12-11 11:11:11' union all

select 8,1,2,'评论',0,'2010-06-11 11:11:11' union all

select 9,2,2,'评论',0,'2010-07-11 11:11:11'

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

update 

  b

set

  score=2

from

  tb a,

  (select id0=row_number()over(partition by uid order by dowhat),* from tb where dowhat='评论')b

where

  a.uid=b.uid

and

  b.id0=1



select * from tb

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

/* id          uid         objid       dowhat score       createtime

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

1           1           1           评论     2           2010-11-11 11:11:11.000

2           1           1           评论     0           2010-01-11 11:11:11.000

3           1           2           评论     0           2010-02-11 11:11:11.000

4           2           1           评论     0           2010-05-11 11:11:11.000

5           2           2           收藏     0           2010-03-11 11:11:11.000

6           2           2           评论     0           2010-04-11 11:11:11.000

7           2           1           评论     0           2010-12-11 11:11:11.000

8           1           2           评论     0           2010-06-11 11:11:11.000

9           2           2           评论     2           2010-07-11 11:11:11.000



(9 行受影响)

*/

--小F-- 2010-04-01

打赏
举报

[code=SQL]update 

  a

set

  score=2

from

  tb a,

  (select id=row_number()over(partiotion by uid order by dowhat),* from tb where dowhat='评论')b

where

  a.uid=b.uid

and

  b.uid=1

[/code]

--小F-- 2010-04-01

打赏
举报

update 

  a

set

  score=2

from

  tb a,

  (select id=row_number()over(partiotion by uid order by dowhat),* from tb)b

where

  a.uid=b.uid

and

  b.uid=1