/******************************************************************************/ /*回复:20080519010总:00030 */ /*主题:行间更新 */ /*作者:二等草 */ /******************************************************************************/set nocount on--数据-------------------------------------------------------------------------- declare @tb table ([id] int,[day] int,[click] int) insert into @tb select 28,17,500 insert into @tb select 56,17,630 insert into @tb select 31,17,200 insert into @tb select 28,18,130 insert into @tb select 56,18,730 insert into @tb select 31,18,130--代码-------------------------------------------------------------------------- select a.id,a.day,click=case when a.day = 17 then a.click else b.click end from @tb a,@tb b where a.id =b.id and ((a.day=17 and b.day=18 and a.click > b.click) or (b.day=17 and a.day=18 and b.click > a.click))
go/*结果-------------------------------------------------------------------------- id day click ----------- ----------- ----------- 28 17 500 31 17 200 28 18 500 31 18 200 --清除------------------------------------------------------------------------*/
/******************************************************************************/
/*回复:20080519010总:00030 */
/*主题:行间更新 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @tb table ([id] int,[day] int,[click] int)
insert into @tb select 28,17,500
insert into @tb select 56,17,630
insert into @tb select 31,17,200
insert into @tb select 28,18,130
insert into @tb select 56,18,730
insert into @tb select 31,18,130--代码--------------------------------------------------------------------------
select a.id,a.day,click=case when a.day = 17 then a.click else b.click end from @tb a,@tb b
where a.id =b.id
and ((a.day=17 and b.day=18 and a.click > b.click)
or (b.day=17 and a.day=18 and b.click > a.click))
go/*结果--------------------------------------------------------------------------
id day click
----------- ----------- -----------
28 17 500
31 17 200
28 18 500
31 18 200
--清除------------------------------------------------------------------------*/