需要对如下数据库表进行查询,
要求:如果查询candidate_id值相同的记录有多个,则只获得createTime最近的一条记录。id candidate_id createBy_id createTime record state type nextLinkTime
90 11 1 2012-2-20 11:53 test 3 A
91 24 1 2012-2-20 11:53 TEST2,TEST2 3 A
92 17 1 2012-2-22 17:37 test3 1 A 2012-2-23 00:00
93 31 1 2012-2-24 12:20 test4 1 A 2012-2-29 00:00
94 31 1 2012-2-24 12:22 test555 1 A
96 32 1 2012-2-24 13:04 TESTk,TESt,test, 1 A 2012-2-24 00:00
97 32 1 2012-2-24 13:05 TEST2,TEST2 0 A 2012-2-25 00:00
希望最终查询结果为:
id candidate_id createBy_id createTime record state type nextLinkTime
90 11 1 2012-2-20 11:53 test 3 A
91 24 1 2012-2-20 11:53 TEST2,TEST2 3 A
92 17 1 2012-2-22 17:37 test3 1 A 2012-2-23 00:00
94 31 1 2012-2-24 12:22 test555 1 A
97 32 1 2012-2-24 13:05 TEST2,TEST2 0 A 2012-2-25 00:00请各位帮忙解决一下,谢谢!
要求:如果查询candidate_id值相同的记录有多个,则只获得createTime最近的一条记录。id candidate_id createBy_id createTime record state type nextLinkTime
90 11 1 2012-2-20 11:53 test 3 A
91 24 1 2012-2-20 11:53 TEST2,TEST2 3 A
92 17 1 2012-2-22 17:37 test3 1 A 2012-2-23 00:00
93 31 1 2012-2-24 12:20 test4 1 A 2012-2-29 00:00
94 31 1 2012-2-24 12:22 test555 1 A
96 32 1 2012-2-24 13:04 TESTk,TESt,test, 1 A 2012-2-24 00:00
97 32 1 2012-2-24 13:05 TEST2,TEST2 0 A 2012-2-25 00:00
希望最终查询结果为:
id candidate_id createBy_id createTime record state type nextLinkTime
90 11 1 2012-2-20 11:53 test 3 A
91 24 1 2012-2-20 11:53 TEST2,TEST2 3 A
92 17 1 2012-2-22 17:37 test3 1 A 2012-2-23 00:00
94 31 1 2012-2-24 12:22 test555 1 A
97 32 1 2012-2-24 13:05 TEST2,TEST2 0 A 2012-2-25 00:00请各位帮忙解决一下,谢谢!
能帮忙解释一下SQL吗,谢谢!
select count(distinct(candidate_id)) from can_linkRecord;
结果为:5475那么:
select count(*) from can_linkRecord a
where not exists(
select 1 from can_linkRecord where a.candidate_id=candidate_id and a.createTime<createTime
);
结果也应该是:5475,但是结果确是:5610首先确认了:
select count(*) from can_linkRecord where candidate_id = '';
结果为:0
也就是没有candidate_id为空的记录。不太明白?
[征集]分组取最大N条记录方法征集,及散分....