select 不及格人数=sum(case jj when '及格' then 0 else 1 end)
,不及格率=cast(cast(sum(case jj when '及格' then 0.0 else 100.0 end)/count(*) as decimal(10,2)) as varchar)+'%'
from 表
,不及格率=cast(cast(sum(case jj when '及格' then 0.0 else 100.0 end)/count(*) as decimal(10,2)) as varchar)+'%'
from 表
declare @var_tab table(id int,chinese int,jj varchar(100))
insert into @var_tab values(1,'61','及格')
insert into @var_tab values(2,'92','及格')
insert into @var_tab values(3,'33','不及格')select 不及格人数=sum(case jj when '及格' then 0 else 1 end)
,不及格率=cast(cast(sum(case jj when '及格' then 0.0 else 100.0 end)/count(*) as decimal(10,2)) as varchar)+'%'
from @var_tab
drop table [dbo].[cj]
gocreate table cj(id int not null primary key,chinese float null, jj varchar(10) null)
goinsert into cj
select 1,61,'及格' union
select 2,92,'及格' union
select 3,33,'不及格'
goselect * from cjselect a.count as 不及格人數, 1.0*a.count/b.count from
(
(select count(*) as count from cj where jj='不及格') a
join
(select count(*) as count from cj ) b
on 1=1
)
drop table [dbo].[cj]
gocreate table cj(id int not null primary key,chinese float null, jj varchar(10) null)
goinsert into cj
select 1,61,'及格' union
select 2,92,'及格' union
select 3,33,'不及格'
goselect a.count as 不及格人數, cast(cast(100.0*a.count/b.count as decimal(10,2)) as varchar(10))+'%' as 不及格率 from
(
(select count(*) as count from cj where jj='不及格') a
join
(select count(*) as count from cj ) b
on 1=1
)