这个语句怎么写?(有点难度)
号码 时间
aaa 2005/12/12
bbb 2006/1/12
aaa 2007/3/3
ccc 2007/3/1
aaa 2007/4/1
bbb 2007/1/1
ddd 2005/1/1
... ...
要求写出一条语句,查出号码中出现次数最多的前2位和最后出现的时间!
就是要这么个结果
号码 时间
aaa 2007/4/1
bbb 2007/1/1
[解决办法]
select top 2 号码,时间 from (select 号码,max(时间),count(1) as cs from table1 group by 号码) t order by cs desc
[解决办法]
select * from ta
where 号码 in(select top 2 号码 from ta group by 号码 order by count(1)desc)
[解决办法]
寫複雜了
Select TOP 2 号码, Max(时间) As 时间 From TabelName Group By 号码 Order By Count(号码) Desc, 时间 Desc
[解决办法]
create table jj
(
code varchar(4),
s_time datetime
)
insert jj select 'aaa ', '2005/12/12 '
union all select 'bbb ', '2006/1/12 '
union all select 'aaa ', '2007/3/3 '
union all select 'ccc ', '2007/3/1 '
union all select 'aaa ', '2007/4/1 '
union all select 'bbb ', '2007/1/1 '
union all select 'ddd ', '2005/1/1 '
select top 2 code ,max(s_time) from jj
group by code
order by count(code) desc