新手求一SQL问题(在线等...)
已经存在一个表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
请问怎么解决????
[解决办法]
select cast(ID as varchar(10)) id,cast(产品ID as varchar(10)) 产品ID,cast(时间 as varchar(10)) 时间, cast(v1 as varchar(10)) v1, cast(v2 as varchar(10)) v2, cast(v3 as varchar(10)) v3, cast(v4 as varchar(10)) v4, cast(sums as varchar(10)) 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
[解决办法]
select (CASE WHEN GROUPING(时间)=1 THEN 'sums ' ELSE CAST(MAX(id) AS CHAR(5)) END),
(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, 1
select 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)