select time, total, convert(varchar(2),round(100*total/sum(total)))+'%' from
(select time, sum(price) as total from table where time between '20040904' and '20040906' group by time) a
--未测试
(select time, sum(price) as total from table where time between '20040904' and '20040906' group by time) a
--未测试
解决方案 »
- 菜鸟有问,求解
- 怎么表示逻辑与
- 安装sql2005的问题
- 多表合并查询求助
- 再发一贴,还是smalldate和datetime类型的问题!
- 请问,有一个字段是字符型的,但里面存的都是数字,我怎么按数字的大小排序输出?
- 求助SQL语句查询,进来看看!
- 如何在C#中用编程方式来建立数据库和表?
- ms sql server 7.0 的replication时的 pull 错误 , 请帮忙看看,谢谢! "无法更新密码。提供给新密码的值包含密码中不允许的值。"
- declare @Pathes[100] varchar(20) 定义数组为什么错?
- 如何取出"2004-10-22 13:33:56"的值?
- 如何比较两个数据库之间的差异。。。。
http://community.csdn.net/Expert/topic/3433/3433671.xml?temp=.6966211
你9月4日的记录有两个,为什么查询结果是这个
2004-9-4 200 21%
from 表
group by time
不知道不对
from 表
group by time
不知道不对
create table tb1(custid int ,time datetime, price int)
insert tb1 select 1,
'2004-9-2' , 100 union all select 2,
'2004-9-2' , 100 union all select 2,
'2004-9-3' , 150 union all select 2,
'2004-9-4' , 180 union all select 1,
'2004-9-4' , 200 union all select 1,
'2004-9-5' , 300 union all select 1,
'2004-9-6' , 400 union all select 1,
'2004-9-21' , 700 union all select 2,
'2004-9-21' , 1000
--语句
declare @sum int
select time, sum(price) as total into #t
from tb1 where custid=1 and time between '20040904' and '20040906' group by time
select @sum=sum(total)
from #t
select time, total, convert(varchar(100),
round(cast(total as decimal(18,2))/@sum,2)*100)+'%' as rate
from #t
drop table #t
--结果time total rate
2004-09-04 00:00:00.000 200 22.0000000000000%
2004-09-05 00:00:00.000 300 33.0000000000000%
2004-09-06 00:00:00.000 400 44.0000000000000%
select @sumprice=sum(price) from tb1
select time,sum(price),cast(sum(price)*100/(@sumprice) as varchar)+'%' from tb1
group by time
having time>'2004-09-03' and time<'2004-09-07'
select @sumprice
----------------------------------------------
2004-09-04 00:00:00.000 380 12%
2004-09-05 00:00:00.000 300 9%
2004-09-06 00:00:00.000 400 12%