原始表是
=====================
主机名 是否合规
A Y
B Y
C N
D UNKNOW
E UNKNOW
======================
sql语句需要得到的结果:==============
Y 40%
N 20%
UNKNOW 40%
==============
=====================
主机名 是否合规
A Y
B Y
C N
D UNKNOW
E UNKNOW
======================
sql语句需要得到的结果:==============
Y 40%
N 20%
UNKNOW 40%
==============
是否合规 ,LTRIM(COUNT(*)*1.0/(SELECT COUNT(*) FROM TB))AS PER
FROM TB GROUP BY 是否合规
--> Author : wufeng4552
--> Date : 2009-11-13 15:35:02
declare @TB table (主机名 nvarchar(2),是否合规 nvarchar(12))
insert into @TB
select 'A','Y' union all
select 'B','Y' union all
select 'C','N' union all
select 'D','UNKNOW' union all
select 'E','UNKNOW'
select 是否合规,
ltrim(cast(count(*)*100.0/(select count(*) from @tb)as dec(18,2)))+'%'
from @tb group by 是否合规
/*
是否合规
------------ ------------------------------------------
N 20.00%
UNKNOW 40.00%
Y 40.00%(3 個資料列受到影響)*/
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(主机名 varchar(10),是否合规 varchar(10))
Go
Insert into TB
select 'A','Y' union all
select 'B','Y' union all
select 'C','N' union all
select 'D','UNKNOW' union all
select 'E','UNKNOW'
Go
--Start
SELECT
是否合规 ,LTRIM(CONVERT(DEC(18,2),COUNT(*)*1.0*100/(SELECT COUNT(*) FROM TB)))+'%' AS PER
FROM TB GROUP BY 是否合规 --Result:
/*
(所影响的行数为 5 行)是否合规 PER
---------- -----------------------------------------
N 20.00%
UNKNOW 40.00%
Y 40.00%(所影响的行数为 3 行)
*/
--End