用SQL命令查询数据库这门课排名前5的同学的学号和成绩若成绩相同按学号升序排列 SQL向每个学生的总成绩,输出学号和总成绩,按总成绩排序(降...
SQL\u6570\u636e\u5e93\uff1a\u67e5\u8be2\u8bfe\u7a0b\u53f7\u4e3a\u201c203402\u201d\u7684\u6210\u7ee9\u6700\u9ad8\u7684\u524d5\u540d\u5b66\u751f\u7684\u5b66\u53f7\u53ca\u6210\u7ee9\uff0c\u7ed3\u679c\u6309\u6210\u7ee9\u964d\u5e8f--\u4e0d\u660e\u767d\u968f\u65f6\u95ee\u6211
select SNO,\u6210\u7ee9 from (
select SNO,\u6210\u7ee9,row_number() over (order by \u6210\u7ee9 desc)*1 bz from tbname where CNO='203402') aa where aa.bz<=5
order by \u6210\u7ee9 desc
select xuehao as \u5b66\u53f7,score as \u603b\u6210\u7ee9 from CJB
where xuehao in(select xuehao from XSB)
group by xuehao,score
order by score desc,xuehao
select top 5 学号,成绩
from 成绩表
where 课程名=‘数据库’
order by 成绩 desc,学号
oracle :
select 学号,成绩
from 成绩表
where 课程名=‘数据库’and rownum<=5
order by 成绩 desc,学号
select top 5 学号,学生,成绩
from 成绩表
where 课程名
order by 成绩 desc,学号
select 学号,成绩, from 学科成绩表 order by 成绩 desc,by 学号
select top 5 *
from student
order by score desc,no asc
no asc的asc可以省略,因为默认是asc的
绛旓細1銆侀鍏堝湪鐢佃剳涓婃墦寮鏁版嵁搴杞欢銆傜劧鍚庨檮鍔犳湁瀛︾敓琛ㄥ拰鎴愮哗琛ㄧ殑鏁版嵁搴撱2銆佺劧鍚庨変腑鏁版嵁搴擄紝鍙抽敭閫夋嫨鏂板缓鏌ヨ銆傚湪鍙宠竟鐨勭┖鐧芥锛岃緭鍏鍛戒护select 瀛︾敓琛.瀛﹀彿,avg(鎴愮哗) as 骞冲潎鍒嗐3銆佸皢瀛︾敓琛ㄥ拰鎴愮哗琛ㄥ叧鑱旇捣鏉ャ傚懡浠や负from 瀛︾敓琛 join 鎴愮哗琛 on 瀛︾敓琛.瀛﹀彿=鎴愮哗琛.瀛﹀彿銆4銆佺敤group by 鍛戒护瀵...
绛旓細select * from table where sex=f and order by score desc 锛3锛夌粺璁℃垚缁╄〃涓敺鐢熴佸コ鐢熺殑浜烘暟锛堣姹傚彇娑堥噸澶嶇殑璁板綍锛夈俿elect distinct count(id) from table 锛4锛夌粺璁″悇闂ㄨ绋鐨勫钩鍧囧垎銆俿elect avg(subject1,suject2...) from table 锛5锛変娇鐢╱nion鏌ヨ淇℃伅0201鐝互鍙婃垚缁╁湪70~89鍒嗕箣闂寸殑璁板綍...
绛旓細鎵鏈夌寮浜鏁版嵁缁撴瀯锛堣〃缁撴瀯锛夌殑SQL璇彞閮芥槸鐧芥惌锛佸厛鍋囪鏁版嵁缁撴瀯涓 瀛︾敓琛紙瀛﹀彿 涓婚敭鎴栬鏈夊敮涓绱㈠紩锛屽鍚嶏紝鎬у埆锛夎绋嬭〃锛堣绋嬪彿 涓婚敭鎴栬鏈夊敮涓绱㈠紩锛岃绋嬪悕锛夐夎琛紙璇剧▼鍙凤紝瀛﹀彿锛-- 瀛楁鈥滆绋嬪彿鈥濆拰鈥滃鍙"璁炬湁鍙屽瓧娈靛敮涓绱㈠紩 鏌ヨ鍑洪変慨浜嗘墍鏈璇剧▼鐨瀛︾敓鐨勫鍙枫佸鍚嶅拰閫変慨闂ㄦ暟锛歴elect a....
绛旓細JOIN R ON S.Sno=R.Sno group by Sname having COUNT(*)>=3 order by 閫変慨璇鹃棬鏁 desc--5insert into S values('S10','璋㈤渾閿','鐢',23,'璁$畻鏈')insert into R values('S10','C03',90)--6update R set Grade=Grade*1.1 where Cno in(select Cno from C where Cname='鏁版嵁搴...
绛旓細寤鸿浣犻鍏堟壘涓鏈SQL鍛戒护闆嗙湅鐪,SQL srever 2000 [瀹夎涔嬪悗,浼氭湁涓涓仈鏈轰粠涔,閭i噷闈㈠彲璋撹灏界殑璁茶堪浜哠QL鍛戒护,娉ㄦ剰:SQL server 2000涓嶴QL鍛戒护鏄噸澶у尯鍒殑.SQL鏄粨鏋勫寲鏌ヨ璁捐璇█鐨勭缉鍐,:SQL server 2000鍒欐槸寰蒋鐨勫叧绯鏁版嵁搴浜у搧,涓鍒囧叧绯绘暟鎹簱閮芥槸渚濊禆SQL鐨.杩欏綋鐒惰鐪嬫暟鎹簱鍐呭缓浜嗗灏慡QL鍛戒护,...
绛旓細select Cname,count(*) as num from Course,(select from SC where Cno in(select Cno from SC group by Cno having Count(*)>50 )) tempTable where Course.Cno=tempTable.Cno group by Course.Cno,Cname order by num asc --2鏌ヨ姣忛棬璇剧殑鎴愮哗閮芥瘮杩欓棬璇鐨勫叾浠栧悓瀛﹂珮鐨勫鐢熺殑瀛﹀彿 select ...
绛旓細--8銆鏌ヨ璇剧▼缂栧彿鈥溾濈殑鎴愮哗姣旇绋嬬紪鍙封溾濊绋嬩綆鐨勬墍鏈夊悓瀛︾殑瀛﹀彿銆佸鍚嶏紱--9銆佹煡璇㈡墍鏈夎绋嬫垚缁╁皬浜60鍒嗙殑鍚屽鐨勫鍙枫佸鍚嶏紱Select Student.SID,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.score<60 --10銆佹煡璇㈡病鏈夊鍏ㄦ墍鏈夎鐨勫悓瀛︾殑瀛﹀彿銆佸鍚嶏紱select Student.Sid,...
绛旓細SQL鏁版嵁搴撳懡浠锛屾眰鏁鏌ヨ骞冲潎鎴愮哗锛乻elect 瀛﹀彿, avg(鍒嗘暟) from grade group by 瀛﹀彿 having count(distinct 璇剧▼缂栧彿) > 1 select 瀛﹀彿 as u_缂栧彿, 濮撳悕 as u_鍚嶇О from student_info where 濮撳悕 like '寮%'union select 璇剧▼缂栧彿 as u_缂栧彿, 璇剧▼鍚嶇О as u_鍚嶇О from curriculum ...
绛旓細where teacher_course.tno=teacher.tno and course.cno=teacher_course.cno and teacher.name like'鍒%';(2)select * from student where birthday>'1990/01/01';(3)select lecture,credit from course where cname='鏁版嵁搴';(4)select teacher_course.classtime,teacher_course.classroom from ...
绛旓細--8缁熻姣闂ㄨ绋鐨勯変慨浜烘暟锛岃緭鍑哄垪鏄庝负璇剧▼鍙凤紝浜烘暟 select cnum as 璇剧▼鍙,count(*) 浜烘暟 from section,sc where section.secnum=sc.secnum group by cnum --9鏌ヨ閫変慨浜鏁版嵁搴鎶鏈紝娌℃湁閫変慨楂樼瓑鏁板鐨勫鐢熷鍚嶅拰绯诲埆 select student.sname,student.dept from student where student.snum in ...