update adminInfo set maxOnline = (select count(*) from userInfo where isOnline = 1), maxDate = GETDATE() where maxOnline < (select count(*) from userInfo where isOnline = 1) and adminId = 1这个语句有两次(select count(*) from userInfo where isOnline = 1) 怎样写只需要查询一次呢?
DECLARE @COUNT INTselect @COUNT=count(*) from userInfo where isOnline = 1update adminInfo set maxOnline = @COUNT, maxDate = GETDATE() where maxOnline < @COUNT and adminId = 1
where maxOnline <@count and adminId = 1
不过楼主要确保where语句查出来是一条数据。否则容易报错。另外,我认为count(1) 更加有效,因为Count(*)需要额外对表进行验证,比如有多少列,这些。
update adminInfo set maxOnline = b.total , maxDate = GETDATE()
from (select count(*) total from userInfo where isOnline = 1) b
where
where maxOnline < b.total and adminId = 1
from (select count(1) total from userInfo where isOnline = 1) b
where maxOnline < b.total and adminId = 1