---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-20 10:33:22 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([kinknm] varchar(2)) insert [tb] select '张' union all select '张' union all select '李' --------------开始查询--------------------------select kinknm,count(1) as num, ltrim(cast((select count(1) from tb where kinknm=t.kinknm)*100*1.0/(select count(1) from tb) as dec(18,2))) from [tb] t group by kinknm ----------------结果---------------------------- /* kinknm num ------ ----------- ----------------------------------------- 李 1 33.33 张 2 66.67(2 行受影响)*/
declare @tb table([kinknm] varchar(2)) insert @tb select '张' union all select '张' union all select '李'select [kinknm],count([kinknm]) , round(count([kinknm])/cast(i as float)*100,0) from @tb t1, (select count(*) as i from @tb) t2 group by [kinknm],i/* kinknm ------ ----------- ---------------------- 李 1 33 张 2 67(2 row(s) affected)
--> 测试数据:@table declare @table table([kinknm] varchar(2)) insert @table select '张' union all select '张' union all select '李' select kinknm,count(1) as num, convert(varchar(20),count(1)*100/(select count(1) from @table))+'%' as ratenm from @table group by kinknm --结果 -------------------------- 李 1 33% 张 2 66%
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-20 10:33:22 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([kinknm] varchar(2)) insert [tb] select '张' union all select '张' union all select '李' --------------开始查询--------------------------select kinknm,count(1) as num, ltrim(ltrim(cast((select count(1) from tb where kinknm=t.kinknm)*100*1.0/(select count(1) from tb) as dec(18,2))) +'%') as ratenm from [tb] t group by kinknm ----------------结果---------------------------- /* kinknm num ratenm ------ ----------- ------------------------------------------ 李 1 33.33% 张 2 66.67%(2 行受影响)*/
declare @tb table([kinknm] varchar(2)) insert @tb select '张' union all select '张' union all select '李'select [kinknm],count([kinknm]) , cast(round(count([kinknm])/cast(i as float)*100,0) as nvarchar(10)) +'%' from @tb t1, (select count(*) as i from @tb) t2 group by [kinknm],i/*kinknm ------ ----------- ----------- 李 1 33% 张 2 67%(2 row(s) affected)
declare @tb table (kinknm nvarchar(10)) insert into @tb select '张' union all select '张' union all select '李' select kinknm ,num=count(*),ratenm=ltrim(cast(count(*)*1.0/(select count(*) from @tb)*100 as decimal(10,0)))+'%' from @tb group by kinknm order by kinknm desckinknm num ratenm ---------- ----------- ------------------------------------------ 张 2 67% 李 1 33%(2 行受影响)
--> 测试数据:@table declare @table table([kinknm] varchar(2)) insert @table select '张' union all select '张' union all select '李' select kinknm,count(1) as num, convert(varchar(20),cast(round(count(1)*100.0/(select count(1) from @table),0) as dec))+'%' as ratenm from @table group by kinknm order by ratenm desc
step 1: Set @tolnum= select count(*) from XXtable
step 2: select kindnm,count(kindnm) as num , count(kindnm)/@tolnum as ratenm from XXtable
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-20 10:33:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([kinknm] varchar(2))
insert [tb]
select '张' union all
select '张' union all
select '李'
--------------开始查询--------------------------select
kinknm,count(1) as num,
ltrim(cast((select count(1) from tb where kinknm=t.kinknm)*100*1.0/(select count(1) from tb) as dec(18,2)))
from
[tb] t
group by
kinknm
----------------结果----------------------------
/* kinknm num
------ ----------- -----------------------------------------
李 1 33.33
张 2 66.67(2 行受影响)*/
declare @tb table([kinknm] varchar(2))
insert @tb
select '张' union all
select '张' union all
select '李'select [kinknm],count([kinknm]) , round(count([kinknm])/cast(i as float)*100,0)
from @tb t1, (select count(*) as i from @tb) t2
group by [kinknm],i/*
kinknm
------ ----------- ----------------------
李 1 33
张 2 67(2 row(s) affected)
--> 测试数据:@table
declare @table table([kinknm] varchar(2))
insert @table
select '张' union all
select '张' union all
select '李'
select kinknm,count(1) as num,
convert(varchar(20),count(1)*100/(select count(1) from @table))+'%' as ratenm
from @table
group by kinknm
--结果
--------------------------
李 1 33%
张 2 66%
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-20 10:33:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([kinknm] varchar(2))
insert [tb]
select '张' union all
select '张' union all
select '李'
--------------开始查询--------------------------select
kinknm,count(1) as num,
ltrim(ltrim(cast((select count(1) from tb where kinknm=t.kinknm)*100*1.0/(select count(1) from tb) as dec(18,2))) +'%') as ratenm
from
[tb] t
group by
kinknm
----------------结果----------------------------
/* kinknm num ratenm
------ ----------- ------------------------------------------
李 1 33.33%
张 2 66.67%(2 行受影响)*/
insert @tb
select '张' union all
select '张' union all
select '李'select [kinknm],count([kinknm]) , cast(round(count([kinknm])/cast(i as float)*100,0) as nvarchar(10)) +'%'
from @tb t1, (select count(*) as i from @tb) t2
group by [kinknm],i/*kinknm
------ ----------- -----------
李 1 33%
张 2 67%(2 row(s) affected)
declare @tb table (kinknm nvarchar(10))
insert into @tb select '张'
union all select '张'
union all select '李'
select kinknm ,num=count(*),ratenm=ltrim(cast(count(*)*1.0/(select count(*) from @tb)*100 as decimal(10,0)))+'%'
from @tb
group by kinknm order by kinknm desckinknm num ratenm
---------- ----------- ------------------------------------------
张 2 67%
李 1 33%(2 行受影响)
--> 测试数据:@table
declare @table table([kinknm] varchar(2))
insert @table
select '张' union all
select '张' union all
select '李'
select kinknm,count(1) as num,
convert(varchar(20),cast(round(count(1)*100.0/(select count(1) from @table),0) as dec))+'%' as ratenm
from @table
group by kinknm
order by ratenm desc
step 2: select kindnm,count(kindnm) as num , count(kindnm)/@tolnum as ratenm from XXtable