小弟,刚学不久,遇到一个超纲问题,度娘问不到然后我就来了这,如题:
表名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怎么编写,谢谢大神.
表名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怎么编写,谢谢大神.
解决方案 »
- 问一个大家可能很能回答上来的问题,关于webbrowser
- 请问,TNmFtp是Delphi自带的控件吗?
- 如何在Delphi调用画好的fastReport报表????????急急急急急急急急急急!!!!!
- 如何将数据库的日期字段用DateTimePicker显示出来,并将DateTimePicker修改的日期写入数据库中
- 如何提高打开数据库的速度?(SQL2000,ADO方式)
- dfgs
- 用API套接字完成Socket通信的范例
- 对数据库进行修改的问题
- 如何同时拥有C++ BUILDER 6.0 和 DELPHI 6.0
- 出售“企业IC卡电子考勤系统”源代码啦!!!毕业设计或商用!!!
- 重"弹"谈Delphi编程中资源文件的应用
- 一个application 如何编译成多个exe程序?
还有性别的字段是什么?Grender? 这不是单词
(
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
(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自己思考吧
8楼, 虽然cast我看不懂,你这个"cnt2"这里报错了我也不知道怎么改,也谢谢你。
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
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