号码 时间
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
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
A.号码,
Max(A.时间) As 时间
From
TabelName A
Inner Join
(Select TOP 2 号码 From TabelName Group By 号码 Order By Count(号码) Desc) B
On A.号码 = B.号码
where 号码 in(select top 2 号码 from ta group by 号码 order by count(1)desc)
Select
A.号码,
Max(A.时间) As 时间
From
TabelName A
Where 号码 In (Select TOP 2 号码 From TabelName Group By 号码 Order By Count(号码) Desc)
group by munber
order by a desc
insert @t 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 col1,max(col2) from @t group by col1 order by count(col1) desc
From Table_Pqs
Group By P_Code
Order By Count(P_Code) Desc
(
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
恩,这个不好办。
inner join (select distinct top 2 id,count(id) as num from tbl
group by id
order by count(id) desc) b
on a.id=b.id
group by b.id