学步园

原帖的意思是说，有一张demo表，其中有三个非主键字段，userid，age和value。现在要根据userid字段来保留同一个userid下age最大的项，其它重复的项删除。我把原帖的数据稍稍改动一下，多增加点重复项，更有助于理解问题。
数据示例：

得出的结果应该是：

我改过的数据得出的结果里，userid为2的有两项，因为age都是6，根据规则，两项都应该保留。

我从几个角度考察这个问题，第一步，先不考虑删除，如果只是要选出按userid分，age最大的项，SQL语句应该怎么写。肯定的一个环节是，我们要先得出每个userid下，最大的那个age是多少，用来作比较的准则。

select   userid,  max(age) as ma   from demo group by userid

结果是：

userid是要保留的字段，在后面我们将看到，如果不保留userid将会在某些情况下出现错误结果。另外要提到的一点是，为了达到“得出每个userid下最大的那个age”这个目标，我们的SQL语句有时候不需要像上面那样写，我后面会举例。我们先用上面这条语句得出一个直观的比较条件作为参考。

接下来要做的事如下图所示：

我们可以用不同的SQL表达方式得出结果：

一、把“select  userid, max(age) as ma  from demo group by userid ”返回的记录集当成临时表，原demo表跟这张临时表做内部联接(inner join) 。

1.  在From子句中指定相等内联接

select   demo.*   from   demo
inner join  (select  userid,  max(age) as ma  from demo
group by userid ) as t
on demo.userid= t.userid  and  demo.age=t.ma
order by demo.userid

2.  在Where子句中指定相等内联接

select  demo.*  from
demo,  (select userid,  max(age) as ma   from demo group by userid ) as t
where  demo.userid = t.userid  and  demo.age = t.ma  
order by demo.userid

二、还有一种表达方法不使用内联接，而使用子查询，看上去更像是一个循环嵌套。也就是我上面说的，得出每个userid下最大的age这个目标不一定用上面提到的语句实现。

select  *  from   demo
where age = (select  max(age)  from  demo  as t  where  t.userid = demo.userid)
order by  demo.userid

这里用了select子查询，用(＝)比较运算符引入的子查询必须返回单个值，扫描demo表时，对于每个demo.userid，“select  max(age)  from  demo  as t  where  t.userid = demo.userid”用到合计函数，可以保证返回单个值，另外注意一下这句子查询里出现了外部表demo，demo表的另一个实例，也就是别名t是内部表。

顺便说一下，像下面的写法是不对的。

select  *  from demo
where age in  (select max(age) from demo group by userid)

因为userid(3) 返回的最大age(4)也出现在userid(2)中，同样，userid(2) 返回的最大age(6)也出现在userid(4)中，最终的结果会多出不符合规则的记录(2，4，34)和(4，6，30)。

到此为止，讲了如何选出我们想要的结果，我们可以把这个结果另存一张表。不过原帖的要求是删除不符合条件的项，删除也是种选择，我们要做的就是选出不符合条件的记录，然后把select改成delete就行了。

符合条件的记录和不符合条件的记录把demo表分成两个集合(collection)，前面讲了用“相等内联接”选出符合条件的记录集，那么外部联接就囊括了“等”和“不等”两个集合，并且把临时表中不匹配的记录的字段设为Null，我们再以Null为where子句的条件就可以找出不符合条件的记录了。

select  demo.*  from  demo
left join  (select  userid, max(age) as ma  from demo group by userid)  as  t
on  demo.userid = t.userid  and  demo.age = t.ma
where  t.userID is null

结果是： 

但是如果对内联接很熟悉的话，我们可以把左外部联接改成“不相等内联接”。

select  demo.* from   demo
inner join  (select userid,max(age) as ma from demo group by userid)  as  t
on  demo.userid = t.userid   and   demo.age < t.ma

效果是同样的。

除了联接，我们同样可以用子查询来实现我们的要求。

select  *  from  demo
where  age < (select  max(age)  from  demo  as  t  where  t.userid = demo.userid)

从执行计划看，都是并行扫描表，排序，汇总，然后循环比较。

对应的delete命令是：

1. delete  from  demo
where  age < (select  max(age)  from  demo  as  t  where  t.userid = demo.userid)
(Access2003中也通过)  

2. delete demo  from   demo
inner join  (select userid,max(age) as ma from demo group by userid)  as  t
on  demo.userid = t.userid   and   demo.age < t.ma

3. delete  demo from demo
left join  (select  userid, max(age) as ma  from demo group by userid)  as  t
on  demo.userid = t.userid  and  demo.age = t.ma
where  t.userID is null