表如下,其中RecTime为DateTime,FieldA为nVarchar
注:此表记录是临时,真实表中记录要超N十万个.
RecTime FieldA
2006-7-10 A
2006-7-20 A
2006-8-10 A
2006-8-10 B
2006-8-10 B
2006-9-5 A
2006-10-10 C
2006-10-10 C
2006-10-10 C
2006-10-10 A要用时间分段(如按月),用SQL语句求得每月中FieldA字段中出现频率最高的字母.即,上述表的结果是:
FResultA FResultB
2006-7 A
2006-8 B
2006-9 A
2006-10 C有几位版友给出了用临时表的解决办法.但如果不用临时表,只用SQL语句(最好是一条),能否搞定呢?谢谢.
注:此表记录是临时,真实表中记录要超N十万个.
RecTime FieldA
2006-7-10 A
2006-7-20 A
2006-8-10 A
2006-8-10 B
2006-8-10 B
2006-9-5 A
2006-10-10 C
2006-10-10 C
2006-10-10 C
2006-10-10 A要用时间分段(如按月),用SQL语句求得每月中FieldA字段中出现频率最高的字母.即,上述表的结果是:
FResultA FResultB
2006-7 A
2006-8 B
2006-9 A
2006-10 C有几位版友给出了用临时表的解决办法.但如果不用临时表,只用SQL语句(最好是一条),能否搞定呢?谢谢.
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 *
from
(
select convert(char(7),RecTime,120) FieldAfrom,count(*) [Count]
from TableName
group by convert(char(7),RecTime,120) ,FieldA
) A
inner join
(
select FieldAfrom,max([Count])[Count]
from
(
select convert(char(7),RecTime,120) FieldAfrom,count(*) [Count]
from TableName
group by convert(char(7),RecTime,120) ,FieldA
) B
group by FieldAfrom
)C
on A.FieldAfrom=B.FieldAfrom and A.[Count]=B.[Count]