select name ,age ,number, rate=count(1)*0.01 from tb
select name ,age ,number, rate=(select count(1) from tb where flag=1)*0.01/count(1) from tb
要计算rate的。是flag=1的记录占总记录的百分比。
select *, ltrim(sum(case when flag=1 then 1 else 0 end)*100.0/count(1))+'%' from tb group by name ,age, number
而且count(1)用在这会报错,其他列无效,没包含在聚合函数或group by子句中,我就是这个地方不知道怎么解决。计算百分比可以用这个函数 (ltrim(cast(((select count(*) from A where flag='1')*100.0/count (*)) as numeric(12,2)))+'%')
写在一句里的话,可以这样: select name,age,number,flag, (case when flag=0 then select sum(case when flag=1 then 1 else 0 end)*1./count(*) from 表A else 0 end)as rate from 表A 但效率奇差! 还不如先算一下统计值: declare @rate decimal(8,2) select @rate=sum(case when flag=1 then 1 else 0 end)*1./count(*) from 表A select name,age,number,flag, (case when flag=0 then @rate else 0 end) as rate from 表A
declare @表A table (name varchar(1),age int,number int,flag int) insert into @表A select 'a',12,4,1 union all select 'b',10,5,1 union all select 'c',12,2,0 union all select 'd',11,1,0 union all select 'e',13,1,0select * from @表A /* name age number flag ---- ----------- ----------- ----------- a 12 4 1 b 10 5 1 c 12 2 0 d 11 1 0 e 13 1 0 */ select *, rate=cast((0.01*(select count(1) from @表A where flag=1)/ (select count(1) from @表A))*100 as decimal(5,2)) from @表A a /* name age number flag rate ---- ----------- ----------- ----------- --------------------------------------- a 12 4 1 0.40 b 10 5 1 0.40 c 12 2 0 0.40 d 11 1 0 0.40 e 13 1 0 0.40 */
select [name] ,age ,number, rate=(select count(1) from tb where flag=1)*0.01/(select count(1) from tb) from tb
CREATE TABLE tb( name VARCHAR(20), age VARCHAR(20),number int,flag int) INSERT INTO tb VALUES ('1',1,123,1) INSERT INTO tb VALUES (2,1,234,0)select [name] ,age ,number, rate=convert(dec(5,2),(select count(1) from tb where flag=1)*0.1/(select count(1) from tb)) from tbdrop table tb--我是觉得没啥意义 /* name age number rate -------------------- -------------------- ----------- --------------------------------------- 1 1 123 0.05 2 1 234 0.05
select *, ltrim(sum(case when flag=1 then 1 else 0 end)*100.0/(select count(1) from tb)+'%' from tb group by name ,age, number
select name ,age ,number,
rate=count(1)*0.01
from tb
select name ,age ,number,
rate=(select count(1) from tb where flag=1)*0.01/count(1)
from tb
*,
ltrim(sum(case when flag=1 then 1 else 0 end)*100.0/count(1))+'%'
from
tb
group by
name ,age, number
(ltrim(cast(((select count(*) from A where flag='1')*100.0/count (*)) as numeric(12,2)))+'%')
select name,age,number,flag,
(case when flag=0 then select sum(case when flag=1 then 1 else 0 end)*1./count(*) from 表A else 0 end)as rate
from 表A
但效率奇差!
还不如先算一下统计值:
declare @rate decimal(8,2)
select @rate=sum(case when flag=1 then 1 else 0 end)*1./count(*) from 表A
select name,age,number,flag,
(case when flag=0 then @rate else 0 end) as rate
from 表A
declare @表A table (name varchar(1),age int,number int,flag int)
insert into @表A
select 'a',12,4,1 union all
select 'b',10,5,1 union all
select 'c',12,2,0 union all
select 'd',11,1,0 union all
select 'e',13,1,0select * from @表A
/*
name age number flag
---- ----------- ----------- -----------
a 12 4 1
b 10 5 1
c 12 2 0
d 11 1 0
e 13 1 0
*/
select *,
rate=cast((0.01*(select count(1) from @表A where flag=1)/
(select count(1) from @表A))*100 as decimal(5,2))
from @表A a
/*
name age number flag rate
---- ----------- ----------- ----------- ---------------------------------------
a 12 4 1 0.40
b 10 5 1 0.40
c 12 2 0 0.40
d 11 1 0 0.40
e 13 1 0 0.40
*/
每行都一样?那一行,flag=0,rate=0.4,而这个0.4却是其他行的统计值,你让看这个结果的人怎么去理解...
select [name] ,age ,number,
rate=(select count(1) from tb where flag=1)*0.01/(select count(1) from tb)
from tb
CREATE TABLE tb( name VARCHAR(20), age VARCHAR(20),number int,flag int)
INSERT INTO tb VALUES ('1',1,123,1)
INSERT INTO tb VALUES (2,1,234,0)select [name] ,age ,number,
rate=convert(dec(5,2),(select count(1) from tb where flag=1)*0.1/(select count(1) from tb))
from tbdrop table tb--我是觉得没啥意义
/*
name age number rate
-------------------- -------------------- ----------- ---------------------------------------
1 1 123 0.05
2 1 234 0.05
*,
ltrim(sum(case when flag=1 then 1 else 0 end)*100.0/(select count(1) from tb)+'%'
from
tb
group by
name ,age, number