create table 表(rectime datetime,fielda char(1))
insert into 表(RecTime,FieldA)
select '2006-7-10','A'
union all select '2006-7-20','A'
union all select '2006-8-10','A'
union all select '2006-8-10','B'
union all select '2006-8-10','B'
union all select '2006-9-5','A'
union all select '2006-10-10','C'
union all select '2006-10-10','C'
union all select '2006-10-10','C'
union all select '2006-10-10','A'select t1.rectime,t2.fielda
from(
select rectime,max(total) total
from(
select convert(char(7),rectime,120) as rectime,fielda,count(*) as total
from 表
group by convert(char(7),rectime,120),fielda)t
group by rectime)t1
left join (
select convert(char(7),rectime,120) as rectime,fielda,count(*) as total
from 表
group by convert(char(7),rectime,120),fielda)t2 on t1.rectime=t2.rectime and t1.total=t2.total
insert into 表(RecTime,FieldA)
select '2006-7-10','A'
union all select '2006-7-20','A'
union all select '2006-8-10','A'
union all select '2006-8-10','B'
union all select '2006-8-10','B'
union all select '2006-9-5','A'
union all select '2006-10-10','C'
union all select '2006-10-10','C'
union all select '2006-10-10','C'
union all select '2006-10-10','A'select t1.rectime,t2.fielda
from(
select rectime,max(total) total
from(
select convert(char(7),rectime,120) as rectime,fielda,count(*) as total
from 表
group by convert(char(7),rectime,120),fielda)t
group by rectime)t1
left join (
select convert(char(7),rectime,120) as rectime,fielda,count(*) as total
from 表
group by convert(char(7),rectime,120),fielda)t2 on t1.rectime=t2.rectime and t1.total=t2.total
(select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num from t
group by convert(char(07),rectime,120) ,FieldA) a
inner join
(select FResultA,max(num) as num
from (select convert(char(07),rectime,120) as FResultA,FieldA as FResultB,
count(*) as num from t
group by convert(char(07),rectime,120) ,FieldA) tt
group by FResultA ) b
on a.FResultA=b.FResultA and a.num=b.num
order by a.FResultA