select ltrim(cast((select count(1) from tb where isnull(签单人,'')!='')*100.0 /(select count(1) from tb) as dec(18,2)))+'%'
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([单号] int,[签单人] varchar(4)) insert [tb] select 1,'张三' union all select 2,'王五' union all select 3,null union all select 4,'李四'
---查询--- select ltrim(cast((select count(1) from tb where isnull(签单人,'')!='')*100.0 /(select count(1) from tb) as dec(18,2)))+'%' ---结果---
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(a int , b varchar(10)) go insert into tb select 1,'张三' union all select 2,'王五' union all select 3,null union all select 4,'李四' go select 百分比=convert (varchar,cast((COUNT(b)*1.00/COUNT(a) )*100 as int))+'%' from tb /*------------(4 行受影响) 百分比 ------------------------------- 75% 警告: 聚合或其他 SET 操作消除了 Null 值。(1 行受影响) -------*/
--> 测试时间:2009-07-11 --> 我的淘宝:http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab] create table [tab]([单号] int,[签单人] varchar(4)) insert [tab] select 1,'张三' union all select 2,'王五' union all select 3,null union all select 4,'李四'select * from [tab]select cast(cast(sum(case when 签单人 is not null then 1 else 0 end)*100.0/count(*) as numeric(8,0)) as varchar(10))+'%' from tab /*
----------- 75%(所影响的行数为 1 行)*/ drop table tab
declare @t table(单号 int ,签单人 varchar(50)) insert into @t select 1, '张三' union all select 2, '王五' union all select 3, null union all select 4, '李四' select ltrim(count(签单人)*100/(select count(*) from @t))+'%' from @t /* 75% */
ltrim(cast((select count(1) from tb where isnull(签单人,'')!='')*100.0
/(select count(1) from tb) as dec(18,2)))+'%'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([单号] int,[签单人] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'王五' union all
select 3,null union all
select 4,'李四'
---查询---
select
ltrim(cast((select count(1) from tb where isnull(签单人,'')!='')*100.0
/(select count(1) from tb) as dec(18,2)))+'%'
---结果---
-----------------------------------------
75.00%(所影响的行数为 1 行)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a int , b varchar(10))
go
insert into tb
select 1,'张三' union all
select 2,'王五' union all
select 3,null union all
select 4,'李四'
go
select 百分比=convert (varchar,cast((COUNT(b)*1.00/COUNT(a) )*100 as int))+'%' from tb
/*------------(4 行受影响)
百分比
-------------------------------
75%
警告: 聚合或其他 SET 操作消除了 Null 值。(1 行受影响)
-------*/
--> 测试时间:2009-07-11
--> 我的淘宝:http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([单号] int,[签单人] varchar(4))
insert [tab]
select 1,'张三' union all
select 2,'王五' union all
select 3,null union all
select 4,'李四'select * from [tab]select cast(cast(sum(case when 签单人 is not null then 1 else 0 end)*100.0/count(*) as numeric(8,0)) as varchar(10))+'%' from tab
/*
-----------
75%(所影响的行数为 1 行)*/
drop table tab
insert into @t
select 1, '张三' union all
select 2, '王五' union all
select 3, null union all
select 4, '李四'
select ltrim(count(签单人)*100/(select count(*) from @t))+'%' from @t
/*
75%
*/