实现功能跟淘宝好评统计一样:
最近一周 最近一个月 最近6个月 6个月前 总计
好评 10 20 30 40 100
中评 10 10 10 10 40
差评 10 10 10 10 40两个表:
买家表,评价表(ID,评价类型(即好,中,差) ,评价时间, 买家外键 )不知道大家有没有什么好又快的方法统计那个结果出来。。
我知道的就是拼接方法,而且是每个格子一条SQL语句(除总计外)。。
最近一周 最近一个月 最近6个月 6个月前 总计
好评 10 20 30 40 100
中评 10 10 10 10 40
差评 10 10 10 10 40两个表:
买家表,评价表(ID,评价类型(即好,中,差) ,评价时间, 买家外键 )不知道大家有没有什么好又快的方法统计那个结果出来。。
我知道的就是拼接方法,而且是每个格子一条SQL语句(除总计外)。。
然后竖着输出就行了。
最后一周
最近一个月
最近6个月
6个月前最次也就4个SQL出来,把4个SQL用Union连进来,一个SQL就OK
case when [datediff <= 30 day] then 1 else 0 end as 一月
case when [datediff <= 180 day] then 1 else 0 end as 六月
from table t自己把中括号中的部分改成对应条件。这样做的好处是只对6个月以内的记录扫描一遍,就能够得出结果。扫表开销明显比union少。
上面的代码有点问题,修改如下
select sum(case when [datediff <= 7 day] then 1 else 0 end) as 一周
sum(case when [datediff <= 30 day] then 1 else 0 end) as 一月
sum(case when [datediff <= 180 day] then 1 else 0 end) as 六月
from table t
另外,代码仅为示例,中括号中部分自己修改成选出日期范围内的好评中评和差评。
rank() over (partition by xxx order by xxx)
id int identity(1,1),
[type] nvarchar(10),
[date] datetime,
CustomID int
)declare @d datetime
set @d = '2009-1-1'
while(datediff(day,@d,getdate()) <> 0)
begin
insert into @temp([type],[date],CustomID)values('好评',@d,1)
insert into @temp([type],[date],CustomID)values('好评',@d,2)
insert into @temp([type],[date],CustomID)values('好评',@d,2)
insert into @temp([type],[date],CustomID)values('好评',@d,3)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,5)
insert into @temp([type],[date],CustomID)values('中评',@d,5)
insert into @temp([type],[date],CustomID)values('差评',@d,4)
set @d = dateadd(day,1,@d);
end;with temp as (
select datediff(day,[date],getdate()) as [days],* from @temp)
select
[type],
sum( case when [days] <=7 then 1 else 0 end) as 一周,
sum( case when [days] <=30 then 1 else 0 end) as 一月,
sum( case when [days] <=(365/2) then 1 else 0 end) as 六个月
from temp
group by [type]
/*
type 一周 一月 六个月
差评 7 30 182
好评 28 120 728
中评 35 150 910
*/
id int identity(1,1),
[type] nvarchar(10),
[date] datetime,
CustomID int
)declare @d datetime
set @d = '2009-1-1'
while(datediff(day,@d,getdate()) <> 0)
begin
insert into @temp([type],[date],CustomID)values('好评',@d,1)
insert into @temp([type],[date],CustomID)values('好评',@d,2)
insert into @temp([type],[date],CustomID)values('好评',@d,2)
insert into @temp([type],[date],CustomID)values('好评',@d,3)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,4)
insert into @temp([type],[date],CustomID)values('中评',@d,5)
insert into @temp([type],[date],CustomID)values('中评',@d,5)
insert into @temp([type],[date],CustomID)values('差评',@d,4)
set @d = dateadd(day,1,@d);
end;with temp as (
select datediff(day,[date],getdate()) as [days],* from @temp)
select
[type],
sum( case when [days] >= 0 and [days] <=7 then 1 else 0 end) as 最近一周,
sum( case when [days] > 7 and [days] <=30 then 1 else 0 end) as 最近一个月,
sum( case when [days] > 30 and [days] <= 183 then 1 else 0 end) as 最近六个月,
sum( case when [days] > 183 then 1 else 0 end) as 六个月前,
sum( 1 ) as 总计
from temp
group by [type]
/*
type 最近一周 最近一个月 最近六个月 六个月前 总计
差评 7 23 153 7 190
好评 28 92 612 28 760
中评 35 115 765 35 950
*/
declare @temp table (
[type] int,
[date] datetime
)declare @d datetime
set @d = '2009-1-1'
while(datediff(day,@d,getdate()) <> 0)
begin
insert into @temp([type],[date])values(1,@d)
insert into @temp([type],[date])values(1,@d)
insert into @temp([type],[date])values(1,@d)
insert into @temp([type],[date])values(1,@d)
insert into @temp([type],[date])values(2,@d)
insert into @temp([type],[date])values(2,@d)
insert into @temp([type],[date])values(2,@d)
insert into @temp([type],[date])values(2,@d)
insert into @temp([type],[date])values(2,@d)
insert into @temp([type],[date])values(3,@d)
set @d = dateadd(day,1,@d);
end;with temp as (
select datediff(day,[date],getdate()) as [days],* from @temp)
select
CASE [type] WHEN 1 THEN '好评' WHEN 2 THEN '中评' WHEN 3 THEN '差评' END AS 评价,
sum( case when [days] >= 0 and [days] <=7 then 1 else 0 end) as 最近一周,
sum( case when [days] > 7 and [days] <=30 then 1 else 0 end) as 最近一个月,
sum( case when [days] > 30 and [days] <= 183 then 1 else 0 end) as 最近六个月,
sum( case when [days] > 183 then 1 else 0 end) as 六个月前,
sum( 1 ) as 总计
from temp
group by [type]
/*
type 最近一周 最近一个月 最近六个月 六个月前 总计
差评 7 23 153 7 190
好评 28 92 612 28 760
中评 35 115 765 35 950
*/
select 评价类型,sum( case when datediff(day,评价时间,getdate()) <=7 then 1 else 0 end) as 最近一周,
sum( case when datediff(day,评价时间,getdate()) <=30 then 1 else 0 end) as 最近一个月,
sum( case when datediff(day,评价时间,getdate()) <= 183 then 1 else 0 end) as 最近六个月,
sum( case when datediff(day,评价时间,getdate()) > 183 then 1 else 0 end) as 六个月前,
sum( 1 ) as 总计
from 评价表 group by 评价类型
BUG是这样的,当所有的数据库记录中没有一条记录是差评的话,那么他查询出来的结果集就只有两行(即好评和中评这两行),但是我想把差评这行记录也补上去,只是这条记录所有列显示的都是0,请问该怎么弄?
BUG是这样的,当所有的数据库记录中没有一条记录是差评的话,那么他查询出来的结果集就只有两行(即好评和中评这两行),但是我想把差评这行记录也补上去,只是这条记录所有列显示的都是0,请问该怎么弄?