3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
求SQL语句
数据库：
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')
go
用一条 SQL语句求出分类各ID'等级'为'1'所占百分比
类似如下结果
1 33。33%
2 25%
3 33。33%
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')
go
用一条 SQL语句求出分类各ID'等级'为'1'所占百分比
类似如下结果
1 33。33%
2 25%
3 33。33%
...全文
请发表友善的回复…
发表回复
andysun88 2007-12-11
select id,per = convert(varchar,convert(float,round(sum(Convert(int,等级)) *100.0/(select sum(Convert(int,等级)) from tb),2),2))+'%'
from tb
group by id
from tb
group by id
jlty7390 2007-12-11
测试下结帖
晓风残月0110 2007-12-11
建议根据你的数据，猜测可能的小数位，
然后decimal(10,10)给定合适的位数
然后decimal(10,10)给定合适的位数
jlty7390 2007-12-11
牛人 多 :)
areswang 2007-12-11
select a.id,rtrim(cast((a.cnt*100/b.cnnt) as numeric(9,2)))+'%' 百分比
from (select id,cast(sum(id) as numeric(9,4)) cnt from tb
where 等级=1
group by id,等级) a
left join (select id,cast(sum(id) as numeric(9,4)) cnnt
from tb group by id) b
on a.id = b.id
from (select id,cast(sum(id) as numeric(9,4)) cnt from tb
where 等级=1
group by id,等级) a
left join (select id,cast(sum(id) as numeric(9,4)) cnnt
from tb group by id) b
on a.id = b.id
晓风残月0110 2007-12-11
--小数保留位的处理
--1
declare @yourFloat float
set @yourFloat = 0.111
select rtrim(cast(@yourFloat * 100 as decimal(10,2))) + '%'
--11.10%
--2
select cast(round(cast(1.0 * 1/3000 * 100 as decimal(10,2)),2) as varchar) + '%'
--0.03%
--使用decimal控制小数的位数，不能控制0的情况如25.00%
--3
declare @str nvarchar(20), @len int
set @str = '0.1200'
set @len = charindex('.',@str)+1
while charindex('0',@str,@len)=@len
begin
set @len=@len+1
end
select substring(@str,1,@len+1)
--可以动态控制位数，但要循环
dawugui 2007-12-11
--不好意思,看错了.
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')
go
select id , 所占百分比 = cast(cast(count(*)*1.0*100/(select count(*) from tb where id = a.id) as decimal(18,2)) as varchar) + '%' from tb a where 等级 = 1 group by id
drop table tb
/*
id 所占百分比
----------- -------------------------------
1 33.33%
2 20.00%
3 33.33%
（所影响的行数为 3 行）
*/kk19840210 2007-12-11
select a.id,left(convert(varchar(100),a.a*1.0/b.b*100),5)+'%' from
(select id,a=count(1) from tb where 等级='1' group by id )a,
(select id,b=count(1) from tb group by id)b
where a.id=b.id
id
----------- -----------
1 33.33%
2 20.00%
3 33.33%
(3 行受影响)
kk19840210 2007-12-11
select a.id,convert(varchar(100),a.a*1.0/b.b*100)+'%' from
(select id,a=count(1) from tb where 等级='1' group by id )a,
(select id,b=count(1) from tb group by id)b
where a.id=b.id
id
----------- -----------------------------------------------------------------------------------------------------
1 33.333333333300%
2 20.000000000000%
3 33.333333333300%
(3 行受影响)
晓风残月0110 2007-12-11
select id,rtrim(cast(round(100.00*count(case when 等级='1' then 1 else null end)/count(1),2) as decimal(10,2))) + '%' 比例 from tb group by id
dawugui 2007-12-11
http://topic.csdn.net/u/20071211/11/d7b56535-9516-429a-9e41-7a045edc0309.html
在这里已经写了.
在这里已经写了.
中国风 2007-12-11
use test
go
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')
go
select
ID,
[百分率]=rtrim(cast(sum(case when 等级=1 then 1 else 0 end)*1.0/(select count(1) from tb where ID=a.ID)*100 as decimal(18,2)))+'%'
from
Tb a
group by ID
ID 百分率
----------- -----------------------------------------
1 33.33%
2 20.00%
3 33.33%
（所影响的行数为 3 行）
chuifengde 2007-12-11
declare @a table(ID int,等级 varchar(10))
insert into @a values(1,'1')
insert into @a values(1,'1')
insert into @a values(1,'2')
insert into @a values(1,'3')
insert into @a values(1,'3')
insert into @a values(1,'2')
insert into @a values(2,'1')
insert into @a values(2,'2')
insert into @a values(2,'3')
insert into @a values(2,'3')
insert into @a values(2,'2')
insert into @a values(3,'1')
insert into @a values(3,'1')
insert into @a values(3,'2')
insert into @a values(3,'3')
insert into @a values(3,'3')
insert into @a values(3,'2')
select id,str(sum(case when 等级='1' then 1 else 0 end)*100.0/count(等级),20,2)+'%' x from @a group by id
--result
/*
id x
----------- ---------------------
1 33.33%
2 20.00%
3 33.33%
*/fcuandy 2007-12-11
create table tb(ID int,等级 varchar(10))
insert into tb values(1,'1')
insert into tb values(1,'1')
insert into tb values(1,'2')
insert into tb values(1,'3')
insert into tb values(1,'3')
insert into tb values(1,'2')
insert into tb values(2,'1')
insert into tb values(2,'2')
insert into tb values(2,'3')
insert into tb values(2,'3')
insert into tb values(2,'2')
insert into tb values(3,'1')
insert into tb values(3,'1')
insert into tb values(3,'2')
insert into tb values(3,'3')
insert into tb values(3,'3')
insert into tb values(3,'2')
go
select id,rtrim(cast(100.00*count(case when 等级='1' then 1 else null end)/count(*) as numeric(10,2))) + '%' 比例 from tb group by id
select id,rtrim(cast(100.00*sum(case when 等级='1' then 1 else 0 end)/count(*) as numeric(10,2))) + '%' 比例 from tb group by id
go
drop table tb
go