UPDATE T_UserLoginRoomLog SET
Area=B.Area,ModelType=B.ModelType,PlatForm = B.PlatForm,Lic = B.Lic
FROM (SELECT TOP 1 * FROM T_UserLoginLog A WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime ORDER BY A.ID DESC) B
WHERE T_UserLoginRoomLog.[Year]='2010' AND T_UserLoginRoomLog.[Month] ='1' AND T_UserLoginRoomLog.[Day]='28'
这样的语法为什么有问题捏,如果我想实现这样的效果,该怎么实现的。要求:每次更新T_UserLoginRoomLog 的一条记录时,去T_UserLoginLog表找到一条记录,见上面的子查询,其中子查询需要用到T_UserLoginRoomLog中当前条记录的字段。
update 和 where 之间居然直接有from 语句,真是有才呀。
updata Table1 set Table1.F1=Table2.F1 from Table1,Table2
where Table1.F2=Table2.F2
--结果,将是null,以下假设只给select top 1 * 查询有结果的记录update
UPDATE T_UserLoginRoomLog SET
Area=(SELECT TOP 1 area FROM T_UserLoginLog A
WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime
ORDER BY A.ID DESC),
ModelType=(SELECT TOP 1 modeltype FROM T_UserLoginLog A
WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime
ORDER BY A.ID DESC),
PlatForm = (SELECT TOP 1 platform FROM T_UserLoginLog A
WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime
ORDER BY A.ID DESC),
Lic = (SELECT TOP 1 lic FROM T_UserLoginLog A
WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime
ORDER BY A.ID DESC)
WHERE T_UserLoginRoomLog.[Year]='2010' AND T_UserLoginRoomLog.[Month] ='1' AND T_UserLoginRoomLog.[Day]='28'
and exists(SELECT * FROM T_UserLoginLog A
WHERE A.UserID=UserLoginRoomLog.PerID AND A.LoginTime<UserLoginRoomLog.RoomTime)
SET
T_UserLoginRoomLog.Lic = B.Lic,
T_UserLoginRoomLog.Area = B.Area,
T_UserLoginRoomLog.ModelType = B.ModelType,
T_UserLoginRoomLog.PlatForm = B.PlatForm
FROM T_UserLoginRoomLog,T_UserLoginLog
WHERE T_UserLoginRoomLog.[Year] = '2010'
AND T_UserLoginRoomLog.[Month] = '1'
AND T_UserLoginRoomLog.[Day] = '28'
AND T_UserLoginLog.UserID = UserLoginRoomLog.PerID
AND T_UserLoginLog.LoginTime = UserLoginRoomLog.RoomTime
.
.
.
如果这样不能保证UserLoginLog表里的数据是唯一的,那就继续追加条件,直到唯一为止
Area=B.Area,
ModelType=B.ModelType,
PlatForm = B.PlatForm,
Lic = B.Lic
FROM T_UserLoginRoomLog A
JOIN ( SELECT Room.PerID, MAX(Log.ID) ID
FROM T_UserLoginLog Log INNER JOIN T_UserLoginRoomLog Room
WHERE Log.UserID=Room.PerID AND Log.LoginTime<Room.RoomTime
GROUP BY Room.PerID ) C ON A.PerID = C.PerID
JOIN T_UserLoginLog B ON C.ID=B.ID
UPDATE T_UserLoginRoomLog SET
Area=B.Area,
ModelType=B.ModelType,
PlatForm = B.PlatForm,
Lic = B.Lic
FROM T_UserLoginRoomLog A
JOIN ( SELECT Room.PerID, MAX(Log.ID) ID
FROM T_UserLoginLog Log INNER JOIN T_UserLoginRoomLog Room
WHERE Log.UserID=Room.PerID AND Log.LoginTime<Room.RoomTime
GROUP BY Room.PerID ) C ON A.PerID = C.PerID
JOIN T_UserLoginLog B ON C.ID=B.ID
WHERE A.[Year]='2010' AND A.[Month] ='1' AND A.[Day]='28'
select
RoomLog.PerID,
max(LoginLog.ID) maxID
into
#NeedUpdate
from
T_UserLoginRoomLog RoomLog
inner join
T_UserLoginLog LoginLog
on
RoomLog.PerID = LoginLog.UserID
and
RoomLog.RoomTime > LoginLog.LoginTime
where
RoomLog.[Year] = '2010'
and
RoomLog.[Month] = '1'
and
RoomLog.[Day] = '28';update
RoomLog
set
RoomLog.Area = LoginLog.Area,
RoomLog.ModelType = LoginLog.ModelType,
RoomLog.PlatForm = LoginLog.PlatForm,
RoomLog.Lic = LoginLog.Lic
from
T_UserLoginRoomLog RoomLog
inner join
#NeedUpdate NeedUpdate
on
RoomLog.PerID = NeedUpdate.PerID
inner join
T_UserLoginLog LoginLog
on
NeedUpdate.maxID = LoginLog.ID;drop table #NeedUpdate;