请教一条统计的SQL语句
表中有result,inserttime个字段
result int类型，有0,1,2等几类值
inserttime datetime类型，是记录插入时间
想按天查询 result每一类值占当天总数的百分比
得到如下结果
2013-09-01 0 20%
2013-09-01 1 30%
2013-09-01 2 50%
2013-09-02 1 60%
2013-09-02 2 40%
[解决办法]
;with cte(inserttime,result) as
(
select '2013-09-01',0
union all select '2013-09-01',1
union all select '2013-09-01',2
union all select '2013-09-02',1
union all select '2013-09-02',2
)
select inserttime,result,cast(cast((cast(typecount as decimal)/everyTotalCount)*100 as int) as varchar)+'%' as rate
from
(
select inserttime,result,COUNT(*) as typecount,
everyTotalCount=(select COUNT(*) from cte b where a.inserttime=b.inserttime)
from cte a
group by inserttime,result
)t
/*
inserttimeresultrate
2013-09-01033%
2013-09-01133%
2013-09-01233%
2013-09-02150%
2013-09-02250%
*/
declare @tb table
(inserttime datetime,result int)
insert @tb
select '2013-09-01',0
union all select '2013-09-01',1
union all select '2013-09-01',2
union all select '2013-09-02',1
union all select '2013-09-02',2
union all select '2013-09-02',2
select
convert(varchar(10),a.inserttime,23) inserttime ,Result, a.count*100/b.count ratio
from
(
select inserttime, Result,
SUM(Case when result=result then 1 end) count
from @tb group by inserttime, Result
) a
join
(
select inserttime,
SUM(Case when result=result then 1 end) count
from @tb group by inserttime) b on a.inserttime=b.inserttime
(6 row(s) affected)
inserttime Result ratio
---------- ----------- -----------
2013-09-01 0 33
2013-09-01 1 33
2013-09-01 2 33
2013-09-02 1 33
2013-09-02 2 66
(5 row(s) affected)
--表中有result,inserttime个字段
--result int类型，有0,1,2等几类值
--inserttime datetime类型，是记录插入时间
--想按天查询 result每一类值占当天总数的百分比
--得到如下结果
--2013-09-01 0 20%
--2013-09-01 1 30%
--2013-09-01 2 50%
--2013-09-02 1 60%
--2013-09-02 2 40%
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
id int identity(1,1) not null,
inserttime datetime null,
result int null
)
Insert Into #t
select '2013-09-01',0 union all
select '2013-09-01',1 union all
select '2013-09-01',2 union all
select '2013-09-01',1 union all
select '2013-09-01',2 union all
select '2013-09-01',0 union all
select '2013-09-01',2 union all
select '2013-09-01',1 union all
select '2013-09-01',2 union all
select '2013-09-01',2 union all
select '2013-09-02',1 union all
select '2013-09-02',2 union all
select '2013-09-02',1 union all
select '2013-09-02',2 union all
select '2013-09-02',1
;with cte_a as(
select inserttime,count(1) as zongji from #t group by inserttime
)
,cte_b as(
select inserttime, result,count(1) as cnt from #t t
group by inserttime,result
)
select b.inserttime,result,cast(cast((cast(cnt as decimal(6,2))/cast(zongji as decimal(6,2)))*100 as decimal) as varchar(100))+'%' as 百分比 from cte_b b left join cte_a a
on a.inserttime=b.inserttime
------------
(15 行受影响)
inserttime result 百分比
----------------------- ----------- -----------------------------------------------------------------
2013-09-01 00:00:00.000 0 20%
2013-09-01 00:00:00.000 1 30%
2013-09-01 00:00:00.000 2 50%
2013-09-02 00:00:00.000 1 60%
2013-09-02 00:00:00.000 2 40%
(5 行受影响)
