我有一个表格列出了安装的软件版本：
id | userid | version | datetime ----+--------+---------+------------------------ 111 | 75 | 10075 | 2013-03-12 13:40:58.770 112 | 75 | 10079 | 2013-03-12 13:41:01.583 113 | 78 | 10065 | 2013-03-12 14:18:24.463 114 | 78 | 10079 | 2013-03-12 14:22:20.437 115 | 78 | 10079 | 2013-03-12 14:24:01.830 116 | 78 | 10080 | 2013-03-12 14:24:06.893 117 | 74 | 10080 | 2013-03-12 15:31:42.797 118 | 75 | 10079 | 2013-03-13 07:03:56.157 119 | 75 | 10080 | 2013-03-13 07:05:23.137 120 | 65 | 10080 | 2013-03-13 07:24:33.323 121 | 68 | 10080 | 2013-03-13 08:03:24.247 122 | 71 | 10080 | 2013-03-13 08:20:16.173 123 | 78 | 10080 | 2013-03-13 08:28:25.487 124 | 56 | 10080 | 2013-03-13 08:49:44.503
解决方法
你没有指定你想要如何处理关系,但是如果你想要重复的显示;
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
