有一表account,字段有 (id,username,pwd,select_count)其中id是主键(int),username,varchar
pwd,varchar
select_count,int 查询的次数该表有10W条数据我现在想得到查询次数最少的前10条数据,selct top 10 * from account order by select_count asc此时我想把上面查出来的10条记录中的select_count+1如何在一条SQL里完成select并update呢?求解决方案
pwd,varchar
select_count,int 查询的次数该表有10W条数据我现在想得到查询次数最少的前10条数据,selct top 10 * from account order by select_count asc此时我想把上面查出来的10条记录中的select_count+1如何在一条SQL里完成select并update呢?求解决方案
update account
set select_count = select_count + 1
where id = (select id from (select top 10 * from account order by select_count asc) as top10)
go
select top 10 id,username,pwd,select_count+1 from account
where id in (select top 10 id from account order by select_count asc )
order by select_count asc
如果不更新数据的情况下,楼上的就可以完成了。如果需要更新数据到数据里面,建议使用存储过程。
create proc proc_xxxx
as
begin
update account
set select_count+1
where id in(
select id from (
select top 10 id
from account
select_count asc ) xx
);
select top 10 * from account order by select_count asc ;
end
OUTPUT DELETED.id INTO #t WHERE id IN(
select top 10 x.id from account x order by select_count ASC,id
);
SELECT * FROM account a WHERE a.id IN(SELECT updateId FROM #t);