您没有指定要如何处理关系,但是如果要显示重复项,将这样做。
SELECT a.* FROM MyTable a
LEFT JOIN MyTable b
ON a.userid=b.userid
AND CAST(a.version AS INT) < CAST(b.version AS INT)
WHERE b.version IS NULL
如果您要消除重复项,并且如果存在重复项,请选择最新的重复项,则必须对查询进行某种程度的扩展;
WITH cte AS (SELECT *, CAST(version AS INT) num_version FROM MyTable)
SELECT a.id, a.userid, a.version, a.datetime
FROM cte a LEFT JOIN cte b
ON a.userid=b.userid
AND (a.num_version < b.num_version OR
(a.num_version = b.num_version AND a.[datetime]<b.[datetime]))
WHERE b.version IS NULL