update 表 set p_id=case when id=1 then 1 else (select max(id) from 表 where price<a.price and id<a.id) end from 表 a
借用zj的修改 declare @表 table(id int,name varchar(10),price int,p_id int) insert into @表(id,name,price) select 2,'hello',3 union all select 3,'good',10 union all select 4,'nice',4 union all select 5,'see',12 union all select 6,'ok',9--更新 update a set p_id=case when id=(select min(id) from @表) then 1 else (select max(id) from @表 where price<a.price and id<a.id) end from @表 a--处理结果 select * from @表
还有的问题 declare @表 table(id int,name varchar(10),price int,p_id int) insert into @表(id,name,price) select 2,'hello',3 union all select 3,'good',10 union all select 4,'nice',4 union all select 5,'see',12 union all select 6,'ok',9 union all select 7,'ok',2--更新 update a set p_id=case when id=(select min(id) from @表) or price =(select min(price) from @表) then 1 else (select max(id) from @表 where price<a.price and id<a.id) end from @表 a--处理结果 select * from @表
--下面是测试--测试数据 declare @表 table(id int,name varchar(10),price int,p_id int) insert into @表(id,name,price) select 1,'hello',3 union all select 2,'good',10 union all select 3,'nice',4 union all select 4,'see',12 union all select 5,'ok',9--更新 update @表 set p_id=case when id=1 then 1 else (select max(id) from @表 where price<a.price and id<a.id) end from @表 a--处理结果 select * from @表/*--测试结果 id name price p_id ----------- ---------- ----------- ----------- 1 hello 3 1 2 good 10 1 3 nice 4 1 4 see 12 3 5 ok 9 3(所影响的行数为 5 行) --*/
else (select max(id) from 表 where price<a.price and id<a.id) end
from 表 a
declare @表 table(id int,name varchar(10),price int,p_id int)
insert into @表(id,name,price)
select 2,'hello',3
union all select 3,'good',10
union all select 4,'nice',4
union all select 5,'see',12
union all select 6,'ok',9--更新
update a set p_id=case when id=(select min(id) from @表) then 1
else (select max(id) from @表 where price<a.price and id<a.id) end
from @表 a--处理结果
select * from @表
declare @表 table(id int,name varchar(10),price int,p_id int)
insert into @表(id,name,price)
select 2,'hello',3
union all select 3,'good',10
union all select 4,'nice',4
union all select 5,'see',12
union all select 6,'ok',9
union all select 7,'ok',2--更新
update a set p_id=case when id=(select min(id) from @表) or price =(select min(price) from @表) then 1
else (select max(id) from @表 where price<a.price and id<a.id) end
from @表 a--处理结果
select * from @表
declare @表 table(id int,name varchar(10),price int,p_id int)
insert into @表(id,name,price)
select 1,'hello',3
union all select 2,'good',10
union all select 3,'nice',4
union all select 4,'see',12
union all select 5,'ok',9--更新
update @表 set p_id=case when id=1 then 1
else (select max(id) from @表 where price<a.price and id<a.id) end
from @表 a--处理结果
select * from @表/*--测试结果
id name price p_id
----------- ---------- ----------- -----------
1 hello 3 1
2 good 10 1
3 nice 4 1
4 see 12 3
5 ok 9 3(所影响的行数为 5 行)
--*/