已经存在一个表temp:
ID 产品ID 时间 v1 v2 v3 v4 sums
1 1001 17:10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1现在想统计上面的数据,得到下面这个样子
ID 产品ID 时间 v1 v2 v3 v4 sums
1 1001 17:10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1
sums 5 1 2 2 0 5
sums 5 20% 50% 50% 0请问怎么解决????
ID 产品ID 时间 v1 v2 v3 v4 sums
1 1001 17:10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1现在想统计上面的数据,得到下面这个样子
ID 产品ID 时间 v1 v2 v3 v4 sums
1 1001 17:10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1
sums 5 1 2 2 0 5
sums 5 20% 50% 50% 0请问怎么解决????
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)) v1,str(sum(v2)) v2,str(sum(v3)) v3 , str(sum(v4)) v4 ,str(sum(sums)) sums from temp
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)/count(1)*100)+'%' v1,str(sum(v2)/count(1)*100)+'%' v2,str(sum(v3)/count(1)*100)+'%' v3 , str(sum(v4)/count(1)*100)+'%' v4 ,'' sums from temp
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)) v1,str(sum(v2)) v2,str(sum(v3)) v3 , str(sum(v4)) v4 ,str(sum(sums)) sums from temp
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)/count(1)*100)+'%' v1,str(sum(v2)/count(1)*100)+'%' v2,str(sum(v3)/count(1)*100)+'%' v3 , str(sum(v4)/count(1)*100)+'%' v4 ,'' sums from temp
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)) v1,str(sum(v2)) v2,str(sum(v3)) v3 , str(sum(v4)) v4 ,str(sum(sums)) sums from temp
union all
select 'sums' ID,count(1) 产品ID,'' 时间, str(sum(v1)/count(1)*100)+'%' v1,str(sum(v2)/count(1)*100)+'%' v2,str(sum(v3)/count(1)*100)+'%' v3 , str(sum(v4)/count(1)*100)+'%' v4 ,'' sums from temp
(CASE WHEN GROUPING(时间)=1 THEN COUNT(1) ELSE MAX(产品ID) END),
时间,sum(v1),sum(v2),sum(v3),sum(v4),sum(sums)
from T GROUP BY 时间 with ROLlUP
(CASE WHEN GROUPING(时间)=1 THEN COUNT(1) ELSE MAX(产品ID) END),
时间,sum(v1),sum(v2),sum(v3),sum(v4),sum(sums)
from T GROUP BY 时间 with ROLlUP
(CASE WHEN GROUPING(时间)=1 THEN COUNT(1) ELSE MAX(产品ID) END),
时间,sum(v1),sum(v2),sum(v3),sum(v4),sum(sums)
from T GROUP BY 时间 with ROLlUP
create table T(ID int, 产品ID char(4), 时间 char(5), v1 int, v2 int, v3 int, v4 int, sums int)
insert T select 1, '1001', '17:10', 0, 0, 0, 0, 0
union all select 2, '1002', '17:20', 1, 0, 1, 0, 2
union all select 3, '1003', '17:30', 0, 1, 0, 0, 1
union all select 4, '1004', '17:40', 0, 0, 1, 0, 1
union all select 5, '1005', '17:50', 0, 1, 0, 0, 1select ID=rtrim(ID), 产品ID, 时间, rtrim(v1), rtrim(v2), rtrim(v3), rtrim(v4), sums from T
union all
select 'sums', count(*), '', rtrim(sum(v1)), rtrim(sum(v2)), rtrim(sum(v3)), rtrim(sum(v4)), sum(sums) from T
union all
select 'sums', count(*), '', rtrim(sum(v1)*100/sum(sums))+'%', rtrim(sum(v2)*100/sum(sums))+'%', rtrim(sum(v3)*100/sum(sums))+'%', rtrim(sum(v4)*100/sum(sums))+'%', NULL from T--result
ID 产品ID 时间 sums
------------ ----------- ----- ------------- ------------- ------------- ------------- -----------
1 1001 17:10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17:40 0 0 1 0 1
5 1005 17:50 0 1 0 0 1
sums 5 1 2 2 0 5
sums 5 20% 40% 40% 0% NULL(7 row(s) affected)