问题描述
今天笔试遇到的数据库题目,大家来看看,怎么做?有student和class两个学生信息和班级信息的表,写出SQL查询语句,求各班中年龄最小的十位同学,并从小排到大。
解决方案
解决方案二:
一点思路也没有,大家有什么想法,一起讨论吧,谢谢大家!
解决方案三:
oracle用rownummysql用limit
解决方案四:
selecttop10.....fromstudenta,classbwhereb.id=a.班级IDorderbyb.agesqlserverselect...from(select...fromstudenta,classbwhereb.id=a.班级IDorderbyb.age)awhererownum<=10oracle
解决方案五:
顶欢迎加入2987841java交流群共同交流面试心得和数据库心得
解决方案六:
mysql>select*fromclass;+------+-----------+|id|name|+------+-----------+|1|信计052班||2|信计051班|+------+-----------+2rowsinset(0.00sec)mysql>select*fromstudent;+------+------+------+------+|id|name|age|cid|+------+------+------+------+|1|张1|18|1||2|张2|20|1||3|张3|17|1||4|张4|18|1||5|张5|16|1||6|张6|19|1||7|张7|15|1||8|刘1|16|2||9|刘2|19|2||10|刘3|21|2||11|刘4|20|2||12|刘5|15|2||13|刘6|17|2||14|刘7|20|2||15|刘8|21|2|+------+------+------+------+15rowsinset(0.00sec)mysql>selects.name,s.age,c.name->fromstudents,classc->wheres.cid=c.idand->6>=(selectcount(*)fromstudentst->wherest.age<=s.ageandst.cid=c.id)->orderbyc.id,s.age;+------+------+-----------+|name|age|name|+------+------+-----------+|张7|15|信计052班||张5|16|信计052班||张3|17|信计052班||张1|18|信计052班||张4|18|信计052班||张6|19|信计052班||刘5|15|信计051班||刘1|16|信计051班||刘6|17|信计051班||刘2|19|信计051班||刘4|20|信计051班||刘7|20|信计051班|+------+------+-----------+12rowsinset(0.00sec)
mysql数据库,由于测试数据没那么多,这里只算了前六位同学。
解决方案七:
排序取前十
解决方案八:
mysql>selects.name,s.age,c.name,->(selectcount(*)fromstudent->wherecid=s.cidandage<=s.age)rank->fromstudents,classc->wheres.cid=c.idand->6>=(selectcount(*)fromstudentst->wherest.age<=s.ageandst.cid=c.id)->orderbyc.id,s.age;+------+------+-----------+------+|name|age|name|rank|+------+------+-----------+------+|张7|15|信计052班|1||张5|16|信计052班|2||张3|17|信计052班|3||张1|18|信计052班|5||张4|18|信计052班|5||张6|19|信计052班|6||刘5|15|信计051班|1||刘1|16|信计051班|2||刘6|17|信计051班|3||刘2|19|信计051班|4||刘4|20|信计051班|6||刘7|20|信计051班|6|+------+------+-----------+------+12rowsinset(0.00sec)
解决方案九:
请问zhoupuyue:6>=(selectcount(*)fromstudentstwherest.age<=s.ageandst.cid=c.id)这句话怎么确定最小的前6位呢?st.age<=s.age表示自己跟自己比较吗?怎么比较的呢?谢谢!
解决方案十:
引用5楼zhoupuyue的回复:
SQLcodemysql>select*fromclass;+------+-----------+|id|name|+------+-----------+|1|信计052班||2|信计051班|+------+-----------+2rowsinset(0.00sec)mysql>select*fromstudent;+------+------+------+------+|id|name|age|cid|+------+------+------+------+|1|张1|18|1||2|张2|20|1||3|张3|17|1||4|张4|18|1||5|张5|16|1||6|张6|19|1||7|张7|15|1||8|刘1|16|2||9|刘2|19|2||10|刘3|21|2||11|刘4|20|2||12|刘5|15|2||13|刘6|17|2||14|刘7|20|2||15|刘8|21|2|+------+------+------+------+15rowsinset(0.00sec)mysql>selects.name,s.age,c.name->fromstudents,classc->wheres.cid=c.idand->6>=(selectcount(*)fromstudentst->wherest.age<=s.ageandst.cid=c.id)->orderbyc.id,s.age;+------+------+-----------+|name|age|name|+------+------+-----------+|张7|15|信计052班||张5|16|信计052班||张3|17|信计052班||张1|18|信计052班||张4|18|信计052班||张6|19|信计052班||刘5|15|信计051班||刘1|16|信计051班||刘6|17|信计051班||刘2|19|信计051班||刘4|20|信计051班||刘7|20|信计051班|+------+------+-----------+12rowsinset(0.00sec)mysql数据库,由于测试数据没那么多,这里只算了前六位同学。
哥们你信计的??哈哈!!!metoo
解决方案十一:
6>=(selectcount(*)fromstudentstwherest.age<=s.ageandst.cid=c.id)st是student表的别名,s也是student表的别名。这部分语句表示在student表中比s表中某一条记录的age小的记录的数量。
解决方案十二:
to#9哈哈!这都被你发现了?
解决方案十三:
mysql>selects.name,s.age,c.name,->(selectcount(*)+1fromstudent->wherecid=s.cidandage<s.age)rank->fromstudents,classc->wheres.cid=c.idand->6>=(selectcount(*)fromstudentst->wherest.age<=s.ageandst.cid=c.id)->orderbyc.id,s.age;+------+------+-----------+------+|name|age|name|rank|+------+------+-----------+------+|张7|15|信计052班|1||张5|16|信计052班|2||张3|17|信计052班|3||张1|18|信计052班|4||张4|18|信计052班|4||张6|19|信计052班|6||刘5|15|信计051班|1||刘1|16|信计051班|2||刘6|17|信计051班|3||刘2|19|信计051班|4||刘4|20|信计051班|5||刘7|20|信计051班|5|+------+------+-----------+------+12rowsinset(0.00sec)
修改一下。
解决方案十四:
回复zhoupuyue:mysql>selects.name,s.age,c.name,->(selectcount(*)+1fromstudent->wherecid=s.cidandage<s.age)rank->fromstudents,classc->wheres.cid=c.idand->6>=(selectcount(*)fromstudentst->wherest.age<=s.ageandst.cid=c.id)->orderbyc.id,s.age;我有点糊涂了,理一理逻辑:原表,S表,c表,st表,可不可以不要st表,换成原表跟s表比较呢?如下:wheres.cid=c.idand6>=(selectcount(*)fromstudentwhereage<=s.ageandcid=c.id)
解决方案十五:
可以。
解决方案:
MK
解决方案:
看不懂数据库的,大家都好厉害啊~
解决方案:
select*fromclass,studentwhereclass.sname=student.snameandstudent.idin//数据库的链接(selecttop10*fromstudent//去前10whereidin(select*fromstudentgroupbysage)//排序)