原帖的意思是说,有一张demo表,其中有三个非主键字段,userid,age和value。现在要根据userid字段来保留同一个userid下age最大的项,其它重复的项删除。我把原帖的数据稍稍改动一下,多增加点重复项,更有助于理解问题。
数据示例:
userid | age | value |
1 | 5 | 45 |
2 | 4 | 34 |
3 | 3 | 54 |
4 | 2 | 54 |
4 | 1 | 23 |
2 | 6 | 55 |
4 | 9 | 28 |
3 | 4 | 27 |
2 | 6 | 70 |
4 | 6 | 30 |
得出的结果应该是:
userid | age | value |
1 | 5 | 45 |
2 | 6 | 55 |
4 | 9 | 28 |
3 | 4 | 27 |
2 | 6 | 70 |
我改过的数据得出的结果里,userid为2的有两项,因为age都是6,根据规则,两项都应该保留。
我从几个角度考察这个问题,第一步,先不考虑删除,如果只是要选出按userid分,age最大的项,SQL语句应该怎么写。肯定的一个环节是,我们要先得出每个userid下,最大的那个age是多少,用来作比较的准则。
select userid, max(age) as ma from demo group by userid
结果是:
userid | ma |
1 | 5 |
2 | 6 |
3 | 4 |
4 | 9 |
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
结果是:
userid | age | value |
2 | 4 | 34 |
3 | 3 | 54 |
4 | 2 | 54 |
4 | 1 | 23 |
4 | 6 | 30 |
但是如果对内联接很熟悉的话,我们可以把左外部联接改成“不相等内联接”。
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