导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求SQL语句

jlty7390 2007-12-11 01:11:53
数据库:
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%

...全文
149 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
jlty7390 2007-12-11
测试下结帖
回复
晓风残月0110 2007-12-11
建议根据你的数据,猜测可能的小数位,
然后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
回复
晓风残月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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告