oracle,怎么由学生Id，查询 年级（或班级）信息？ sql语句:已知学生表student 字段学生id 学生姓名...
oracle \u65b9\u6cd5\u5982\u4f55\u6839\u636eid\u67e5\u8be2\u5b66\u751f\u4fe1\u606f\u5177\u4f53\u95ee\u4ec0\u4e48\uff0cID\u662f\u67e5\u8be2\u6761\u4ef6\uff1f select * from student where id =''
oracle.(\u4e0esql server\u7684sql\u8bed\u53e5\u4e3b\u8981\u5728\u51fd\u6570\u4e0a\uff0c\u6211\u8bb0\u5f97sql server\u6ca1\u6709nvl \u6709nullif)
(1)
select b.\u73ed\u7ea7\u540d\u79f0,a.\u8bfe\u7a0b\u540d\u79f0,a.\u5e73\u5747\u5206 from (select \u73ed\u7ea7id,\u8bfe\u7a0b\u540d\u79f0,avg(nvl(\u8003\u8bd5\u6210\u7ee9,0)) \u5e73\u5747\u5206 from student group by \u73ed\u7ea7id,\u8bfe\u7a0b\u540d\u79f0) a,class b where a.\u73ed\u7ea7id=b.\u73ed\u7ea7id
(2)
select * from student, (select \u5b66\u751f\u59d3\u540d,count(*) from student where \u8003\u8bd5\u6210\u7ee92) a where \u8003\u8bd5\u6210\u7ee9<60 and student.\u5b66\u751f\u59d3\u540d=a.\u5b66\u751f\u59d3\u540d
绛旓細杩欐槸鍏崇郴鏁版嵁搴撻棶棰橈紝姣旇緝绠鍗1.鍏堝仛骞寸骇grade琛ㄥ瓧娈 gra_id(骞寸骇缂栫爜锛屾槸鍞竴鐨勶級锛実ra_name锛堝勾绾у悕绉帮級2.鐝骇class琛,cla_id(鐝骇缂栫爜锛屽敮涓鐨勶級锛実ra_id锛堜笌骞寸骇琛ㄥ叧鑱旓級锛宑la_name锛堢彮绾у悕绉帮級3.瀛︾敓student琛紝stu_id(瀛︾敓缂栫爜锛屽敮涓鐨勶級锛宑la_id锛堜笌鐝骇琛ㄥ叧鑱旓級锛宻tu_name锛宻tu_xb锛堢瓑绛...
绛旓細鍍oracle杩欐牱鐨勬暟鎹簱 鏈夊緢澶氬唴缃嚱鏁 鍙互杞绘澗瑙ｅ喅闂锛沎TEST2@orcl#25-2鏈 -11] SQL>select id,cuser_id,rank()over(partition by cuser_id order by score desc) from course;ID CUSER_ID RANK()OVER(PARTITIONBYCUSER_IDORDERBYSCOREDESC)--- --- --- 2 1 1 1 1 ...
绛旓細select a.瀛︾敓id, a.濮撳悕,max(case when b.绉戠洰缂栫爜 = '1' then b.绉戠洰鎴愮哗 else 0 end) 璇枃鎴愮哗,max(case when b.绉戠洰缂栫爜 = '2' then b.绉戠洰鎴愮哗 else 0 end) 鏁板鎴愮哗,max(case when b.绉戠洰缂栫爜 = '3' then b.绉戠洰鎴愮哗 else 0 end) 鑻辫鎴愮哗 from a,b where a.瀛︾敓id =...
绛旓細(1)select b.鐝骇鍚嶇О,a.璇剧▼鍚嶇О,a.骞冲潎鍒 from (select 鐝骇id,璇剧▼鍚嶇О,avg(nvl(鑰冭瘯鎴愮哗,0)) 骞冲潎鍒 from student group by 鐝骇id,璇剧▼鍚嶇О) a,class b where a.鐝骇id=b.鐝骇id (2)select * from student, (select 瀛︾敓濮撳悕,count(*) from student where 鑰冭瘯鎴愮哗<60 group by...
绛旓細stuid varchar2(32), code varchar2(32), score number );comment on table stu_info is '瀛︾敓淇℃伅琛';comment on table course_info is '璇剧▼淇℃伅琛';comment on table score_info is '鍒嗘暟琛';alter table stu_info add constraint pk_stu_info_id primary key (stuid);alter t...
绛旓細锛圥s锛氬伓骞虫椂宸ヤ綔灏辨槸鍋氳璁″拰寮鍙戠殑锛屼互涓嬫墍鏈塖QL缁忚繃oracle娴嬭瘯锛夊鐢熻〃 Student锛瀛︾敓ID锛堜富閿級銆佸鐢熶唬鐮併佸鐢熷悕绉般佸鐢ㄥ瓧娈1銆佸鐢ㄥ瓧娈2銆佸鐢ㄥ瓧娈3锛涜绋嬭〃 Class:璇剧▼ID锛堜富閿級銆佽绋嬩唬鐮併佽绋嬪悕绉般佸鐢ㄥ瓧娈1銆佸鐢ㄥ瓧娈2銆佸鐢ㄥ瓧娈3锛汳APPING琛 StuClass:涓婚敭ID銆佸鐢烮D銆佽绋婭D銆-- Create...
绛旓細浣犲彲浠ュ啀OEM涓垱寤鸿〃锛屽彲瑙嗗寲锛屾柟渚挎槗淇敼锛
绛旓細select (case 鎬у埆 when 1 then '鐢' when 2 then '濂' else '鏈煡' end) as 鎬у埆鍚嶇О from 琛ㄥ悕 where 鏉′欢 order by 鎺掑簭瀛楁鍚
绛旓細1. 鍏堝垱寤轰竴涓瀛︾敓琛╯tudent锛岃缃〃瀛楁s_id锛堝鐢熺紪鍙凤紝涓婚敭锛夈乶ame锛堝鐢熷鍚嶏級銆乻_class锛堢彮绾э紝涓昏灞炴у瓧娈靛懡鍚嶉伩鍏Oracle鍏抽敭瀛楋級銆乤ge锛堝勾榫勶級銆亀eight锛堜綋閲嶏級銆乭eight锛堣韩楂橈級绛 2. 鏌ヨ璇彞锛
绛旓細杩欎釜鍙互鎷垮埌骞跺垪鍚嶆锛孲ELECT A.id, A.name, A.age, A.score FROM (select subject, id, score, rank() over(partition by subject order by score desc) rk from B) t,A WHERE t.rk <= 2 AND A.id = t.id 濡傛灉涓嶈骞跺垪鍚嶆锛屾妸閭ｄ釜rank()鏀逛负row_number()鍗冲彲 ...
