字段: id | vote | pubdate | pid 1 1 2008-11-16 15
2 1 2008-11-17 15
3 1 2008-11-16 14
4 1 2008-11-12 13
5 1 2008-11-12 12
6 1 2008-11-12 15
7 1 2008-11-12 15
7 1 2008-11-12 12
如何统计本周内 vote最多的pid?请sql达人指教
2 1 2008-11-17 15
3 1 2008-11-16 14
4 1 2008-11-12 13
5 1 2008-11-12 12
6 1 2008-11-12 15
7 1 2008-11-12 15
7 1 2008-11-12 12
如何统计本周内 vote最多的pid?请sql达人指教
Select * From t where datepart(week,pubdate)=datepart(week,getdate()) and datepart(year,pubdate)=datepart(year,getdate())
Select top 1 count(1),pib From t where datepart(week,pubdate)=datepart(week,getdate()) and datepart(year,pubdate)=datepart(year,getdate())
group by pib order by count(1) desc
--没有在环境中测试,不保证对
insert @t select 1 , 1 , '2008-11-16' , 15
insert @t select 2 , 1 , '2008-11-17' , 15
insert @t select 3 , 1 , '2008-11-16' , 14
insert @t select 4 , 1 , '2008-11-12' , 13
insert @t select 5 , 1 , '2008-11-12' , 12
insert @t select 6 , 1 , '2008-11-12' ,15
insert @t select 7 , 1 , '2008-11-12' ,15
insert @t select 7 , 1, '2008-11-12' ,12
select * from (
select pid,count(*)cnt from @t where datepart(week,pubdate)=datepart(week,getdate()) and datepart(year,pubdate)=datepart(year,getdate())
group by pid)tt
where cnt=(
select max(cnt) from (
select pid,count(*)cnt from @t where datepart(week,pubdate)=datepart(week,getdate()) and datepart(year,pubdate)=datepart(year,getdate())
group by pid)t )
/*pid cnt
----------- -----------
15 2
*/
insert #RR select 1,1,'2008-11-16',15
insert #RR select 2,1,'2008-11-17',15
insert #RR select 3,1,'2008-11-16',14
insert #RR select 4,1,'2008-11-12',13
insert #RR select 5,1,'2008-11-12',12
insert #RR select 6,1,'2008-11-12',15
insert #RR select 7,1,'2008-11-12',15
insert #RR select 7,1,'2008-11-12',12
--如何统计本周内 vote最多的pid?
SELECT max(vote),pid from #RR where pubdate between DATEADD(wk,DATEDIFF(wk,0,getdate()),0) and DATEADD(wk,DATEDIFF(wk,0,getdate()),0) group by pid
declare @t table (id int ,vote int,pubdate datetime,pid int)
insert into @t select 1,1,'2008-11-16',15
union all select 2,1,'2008-11-17',15
union all select 3,1,'2008-11-16',14
union all select 4,1,'2008-11-12',13
union all select 5,1,'2008-11-12',12
union all select 6,1,'2008-11-12',15
union all select 7,1,'2008-11-12',15
union all select 7,1,'2008-11-12',12
select top 1 vote,pid from @t where pubdate>= dateadd(day,-7,getdate())
group by vote,pid
order by count(*) desc 1 15