在这个视图中时按时间查询重复数据中最新数据,其余数据不变！求解！该怎么解决
日期：2014-05-18 浏览次数：20584 次
在这个视图中时按时间查询重复数据中最新数据,其余数据不变！求解！
------解决方案--------------------
--假设按照UID取最新的CreateTime
select t.* from [dbo].[GetAllUser] t where CreateTime = (select max(CreateTime) from [dbo].[GetAllUser] where UID = t.UID)
select t.* from [dbo].[GetAllUser] t where not exists (select 1 from [dbo].[GetAllUser] where UID = t.UID and CreateTime > t.CreateTime)
- SQL code
ALTER VIEW [dbo].[GetAllUser]
AS
SELECT Users_1.UID, Users_1.Email, Users_1.Mobile, Users_1.UserID, Users_1.PassWord, Users_1.UserRank, Users_1.IsState, Users_1.IsVIP,dbo.UserInfo.CreateTime,
CASE WHEN charindex('.', '' + r.RUID + '') > 0 THEN replace(r.RUID, 'http://', '') WHEN r.RUID IS NULL THEN '--' ELSE
(SELECT UserID
FROM dbo.Users
WHERE (UID = r.RUID)) END AS RUserID, v.ValPhone, dbo.UserInfo.QQ,udb.Reasons,udb.DisableTime
FROM dbo.Users AS Users_1 INNER JOIN
dbo.UserInfo ON Users_1.UID = dbo.UserInfo.UID LEFT OUTER JOIN
dbo.ReferrerInfo AS r ON Users_1.UID = r.UID LEFT OUTER JOIN
dbo.UserValidation AS v ON Users_1.UID = v.UID LEFT OUTER JOIN
dbo.UsersDisable as udb on Users_1.UID=udb.UID
GO
------解决方案--------------------
--假设按照UID取最新的CreateTime
select t.* from [dbo].[GetAllUser] t where CreateTime = (select max(CreateTime) from [dbo].[GetAllUser] where UID = t.UID)
select t.* from [dbo].[GetAllUser] t where not exists (select 1 from [dbo].[GetAllUser] where UID = t.UID and CreateTime > t.CreateTime)
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
