请教一个SQL查询“男女比例”语句

[9S]

小弟，刚学不久，遇到一个超纲问题，度娘问不到然后我就来了这，如题:
表名Class,列名ClassID,ClassName。
表名Student,列名StudentID,StudentName,Grender,Age,ClassID
两个表的ClassID是关联的，Class是主表
问：
--计算各个班级的男女比例（用百分比表示结果）
结果如下(本来想截图，各种不能访问，我吐血)
classID ClassName Percent
1 软件开发01班 200%
3 软件开发02班 100%
2 中文系01班 50%

求在T-SQL怎么编写，谢谢大神.

[9S]

我等啊，等啊，没人回答。。

[kaikai_kk]

男女比例是占总人数比例还是男:女
还有性别的字段是什么？Grender? 这不是单词

[lzg827]

我承认今天晚上很无聊，居然去构造数据去做这个无聊的题目。。。。。
仅纪念逝去的学生年代。。。
和楼主一起骂出题的老师吧，做出来其实并不难，可优化的地方很多，这才是看能力的地方。
我就贴个最笨的方法吧

[lzg827]

建表语句

create table CLASS
(
  CLASSID   VARCHAR2(10) not null,
  CLASSNAME VARCHAR2(10)
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table CLASS
  add constraint PK_CLASS primary key (CLASSID)

-- Create table
create table STUDENT
(
  STUDENTID   VARCHAR2(10) not null,
  STUDENTNAME VARCHAR2(10),
  GRENDER     VARCHAR2(10),
  AGE         VARCHAR2(10),
  CLASSID     VARCHAR2(10)
)
tablespace RCDATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table STUDENT
  add constraint FK_STUDENT_CLASSID foreign key (CLASSID)
  references CLASS (CLASSID);

构造数据

insert into CLASS (CLASSID, CLASSNAME)
values ('001', '软开01班');
insert into CLASS (CLASSID, CLASSNAME)
values ('002', '软开02班');
insert into CLASS (CLASSID, CLASSNAME)
values ('003', '中文01班');

insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00101', '张飞', '男', '20', '001');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00102', '吕布', '男', '25', '001');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00103', '貂蝉', '女', '16', '001');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00201', '刘备', '男', '30', '002');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00202', '诸葛亮', '男', '30', '002');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00203', '大乔', '女', '17', '002');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00204', '小乔', '女', '16', '002');
insert into STUDENT (STUDENTID, STUDENTNAME, GRENDER, AGE, CLASSID)
values ('00301', '凤姐', '女', '25', '003');

查询语句

select a.classid,a.classname,
(select count(1) from student b where a.classid =b.classid and b.grender = '男') as 男生,
(select count(1) from student b where a.classid =b.classid and b.grender = '女') as 女生,
 (select count(1) from student b where a.classid =b.classid and b.grender = '男')/(select count(1) from student b where a.classid =b.classid and b.grender = '女') *100 ||'%' as 男女比
from class a

[lzg827]

百分号的那个函数忘了，留个楼主优化吧。
今天真是无聊透顶。。。。

[bdmh]

when case语法，统计出男女人数，然后算个比例就是了

[9S]

我就是不会写这个Case语法，还没学的

[kaikai_kk]

--SQLServer:

select A.ClassID,C.ClassName,cast(round(A.cnt1/A.cnt2*100,0) as varchar(10))+'%' from
(select ClassID,count(1) cnt1 from Student where Grender='男' group by ClassID) A inner join
(select ClassID,count(1) cnt2 from Student where Grender='女' group by ClassID) B
on A.ClassID=B.ClassID 
left join Class C 
on A.ClassID=C.ClassID

没有处理全部是男或者是全部是女的情况,LZ自己思考吧

[9S]

6楼，Thank you!
8楼， 虽然cast我看不懂，你这个"cnt2"这里报错了我也不知道怎么改，也谢谢你。

[可以飞翔]

select ClassID,ClassName,cast(cast(man as float)/cast(woman as float)*100 as varCHAR(10))+'%' as 'Persent' from ( select ClassID,ClassName,max(case grender when 0 then counts else 0 end) as 'man', MAX(case Grender when 1 then counts else 0 end) as 'woman' from( select Grender,Class.ClassName,class.ClassID,COUNT(*)as counts from Student join Class on Class.ClassID=Student.ClassID group by Grender,class.ClassID,ClassName )as m group by ClassID,ClassName ) as n

[lanshanhezhiyong]

postgresql 写法： select a.classId, a.className,(sum(case b.gender when '男' then 1 else 0 end)*100/sum(case b.gender when '女' then 1 else 0 end))||'%' from Class a left join Student b on a.classID=b.classID group by a.classID,a.className

[昨天没吃饭]

虽然cast我看不懂，你这个"cnt2"这里报错了我也不知道怎么改，也谢谢你