declare @t table(班级 varchar(10),性别 varchar(10)) insert into @t select '1班','男' insert into @t select '1班','男' insert into @t select '1班','女' insert into @t select '2班','女' insert into @t select '2班','男' select 班级, rtrim(cast(sum(case 性别 when '男' then 1 else 0 end)*100.0/count(*) as int))+'%' 男生比例, rtrim(cast(sum(case 性别 when '女' then 1 else 0 end)*100.0/count(*) as int))+'%' 女生比例 from @t group by 班级/* 班级 男生比例 女生比例 ---------- ------------- ------------- 1班 66% 33% 2班 50% 50% */
DECLARE @T TABLE(A VARCHAR(4) , B VARCHAR(4) ,ID INT) INSERT INTO @T SELECT '1班' , '男',1 UNION SELECT '1班' , '男',2 UNION SELECT '1班' , '女' ,3 UNION SELECT '2班' , '女' ,4 UNION SELECT '2班' , '男',5SELECT A , SUM( CASE B WHEN '男' THEN 1 END)/SUM( CASE B WHEN '女' THEN 1 END) AS PERCENT1 FROM @T GROUP BY A SELECT A, (SELECT COUNT(A) FROM @T C WHERE C.A = E.A AND C.B = '男' ) /(SELECT COUNT(A) FROM @T D WHERE D.A = E.A AND D.B = '女' ) AS PERCENT1 FROM @T E GROUP BY A
insert into @t select '1班','男'
insert into @t select '1班','男'
insert into @t select '1班','女'
insert into @t select '2班','女'
insert into @t select '2班','男'
select
班级,
rtrim(cast(sum(case 性别 when '男' then 1 else 0 end)*100.0/count(*) as int))+'%' 男生比例,
rtrim(cast(sum(case 性别 when '女' then 1 else 0 end)*100.0/count(*) as int))+'%' 女生比例
from
@t
group by
班级/*
班级 男生比例 女生比例
---------- ------------- -------------
1班 66% 33%
2班 50% 50%
*/
DECLARE @T TABLE(A VARCHAR(4) , B VARCHAR(4) ,ID INT)
INSERT INTO @T
SELECT '1班' , '男',1 UNION
SELECT '1班' , '男',2 UNION
SELECT '1班' , '女' ,3 UNION
SELECT '2班' , '女' ,4 UNION
SELECT '2班' , '男',5SELECT A , SUM( CASE B WHEN '男' THEN 1 END)/SUM( CASE B WHEN '女' THEN 1 END) AS PERCENT1 FROM @T
GROUP BY A SELECT A,
(SELECT COUNT(A) FROM @T C WHERE C.A = E.A AND C.B = '男' ) /(SELECT COUNT(A) FROM @T D WHERE D.A = E.A AND D.B = '女' )
AS PERCENT1
FROM @T E
GROUP BY A